Re: [SQL] Insert Function
On Wednesday 10 July 2002 21:59, David Durst wrote: > Is there anyway to create a insert function? > I am trying: > CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) > RETURNS int4 AS 'INSERT INTO usr > (user_name,first_name,last_name,permission_set_id,customer_id) values > ($1,$2,$3,$4,$5)' language 'sql'; > > and get: > > ERROR: function declared to return integer, but final statement is not a > SELECT > I thought that a insert would return a internal row #, but I am not sure > about this. You can do something like this: CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) RETURNS VARCHAR AS 'INSERT INTO usr (user_name,first_name,last_name,permission_set_id,customer_id) VALUES ($1,$2,$3,$4,$5); SELECT ''created user ''|| $1::VARCHAR' language 'sql'; HTH Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Insert Function
On Wednesday 10 July 2002 23:04, David Durst wrote: > I am not sure what the end select does, so if you can give me a explination > it would be apreciated It is there to satisfy the requirement that the function should return a value from a select statement: "12.2. Query Language (SQL) Functions SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list, which must be a SELECT. ..." (see http://www.postgresql.org/idocs/index.php?xfunc-sql.html ) After an INSERT statement you need to provide an arbitrary select statement, which could return anything. All the example does is return a string telling you what you've just done. Ian Barwick > > On Wednesday 10 July 2002 21:59, David Durst wrote: > >> Is there anyway to create a insert function? > >> I am trying: > >> CREATE FUNCTION > >> add_user(varchar(20),varchar(20),varchar(20),int4,int4) RETURNS int4 > >> AS 'INSERT INTO usr > >> (user_name,first_name,last_name,permission_set_id,customer_id) values > >> ($1,$2,$3,$4,$5)' language 'sql'; > >> > >> and get: > >> > >> ERROR: function declared to return integer, but final statement is > >> not a SELECT > >> I thought that a insert would return a internal row #, but I am not > >> sure about this. > > > > You can do something like this: > > > > CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4) > > RETURNS VARCHAR AS > > 'INSERT INTO usr > > (user_name,first_name,last_name,permission_set_id,customer_id) > > VALUES > > ($1,$2,$3,$4,$5); > > SELECT ''created user ''|| $1::VARCHAR' > > language 'sql'; > > > > HTH > > > > Ian Barwick > > > > > > ---(end of > > broadcast)--- TIP 5: Have you checked our > > extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] list of tables ?
On Thursday 11 July 2002 16:00, Steve Brett wrote: > can anyone point me in the right direction ? > > i need to list all the tables in a database. > > i've looked at pgadmin_tables which is empty and pga_schema whihc contains > a sinlge row i don't want to parse ... > > is there an easier way t get a list of tables ? Start psql with the -E option and issue \dt This gives you the statement(s) used internally by psql. Ian Barwick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> Are string comparisons in postgresql case sensitive?
Yes, unless you specify otherwise.
Are you sure you are using the right database? I can
reproduce similar results, but only like this:
mysql> create temporary table foo (ch char(2), vc varchar(2));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into foo values ('aa','AA');
Query OK, 1 row affected (0.02 sec)
mysql> select * from foo where ch = 'aa';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.01 sec)
mysql> select * from foo where ch = 'AA';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.00 sec)
mysql> select * from foo where vc = 'aa';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.00 sec)
Regards
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Friday 27 September 2002 01:14, Chris wrote: > >On Thursday 26 September 2002 19:54, Jordan Reiter wrote: > > > Are string comparisons in postgresql case sensitive? > > > >Yes, unless you specify otherwise. > > > >Are you sure you are using the right database? I can > >reproduce similar results, but only like this: > > You're using MySQL in these examples .. not Postgres :) Full points for paying attention ;-) This, erm, characteristic of the former caused me a lot of grief once... Anyone know what the ANSI standard is? I don`t recall any other database apart from MySQL which default to case-insensitive CHAR or VARCHAR columns. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Friday 27 September 2002 05:19, Tom Lane wrote: > Ian Barwick <[EMAIL PROTECTED]> writes: > > Anyone know what the ANSI standard is? I don`t recall any other > > database apart from MySQL which default to case-insensitive > > CHAR or VARCHAR columns. > > I believe the spec has a notion of a "collation attribute" attached > to character-type columns. You could define a collation that makes > comparisons case insensitive and then mark selected columns that way. > We don't have anything like that yet, though Tatsuo has been heard > muttering about how to make it happen ... For reference, MySQL treats CHAR and VARCHAR columns as case insensitive by default; to be treated as case sensitive, fields must be defined or redefined as CHAR BINARY / VARCHAR BINARY. Personally I prefer handling case (in)sensitivity explicitly in the WHERE clause or at application level, though if the standard allows it and it's optional, enabling specific columns to be case insensitive in comparisions can only be a Good Thing (TM). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/pgsql
On Wednesday 25 September 2002 21:38, Gustavo Tadao Okida wrote: (...) > After this, I wrote a function with > pgaccess : > > Name: spi_novo_parametro paramters: > returns: char language: plpgsql > > DECLARE > val INTEGER :=1; > BEGIN > insert into identificador_pri values(1, "OK"); > RETURN "OK"; > END; > > It was OK when I saved it in pgaccess front end. > > When I tested it, by query tab, writing select spi_novo_parametro();, it > return me this message: > > Error: can't read > "PgAcVar(mw,.pgaw:22,nrecs)": no such element in array (...) I don't know PgAccess, but possibly your function is incorrect and the errors produced on execution are confusing PgAccess? Try defining and executing the function in the psql command line client. This may give you a better idea of what is happening. I would also strongly recommend replacing the double quotes with pairs of single quotes, e.g. ''OK'' instead of "OK" . Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Passing array to PL/SQL and looping
On Friday 27 September 2002 18:04, Josh Berkus wrote:
> Greg,
>
> > CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS '
> > DECLARE
> > return_array VARCHAR[];
> > BEGIN
> > return_array[0] := ''test'';
> > return_array[1] := ''test 1'';
> > return_array[2] := ''test 2'';
> > RETURN (return_array);
> > END;'
> > LANGUAGE 'plpgsql';
>
> No, it's not possible to do the above. This is a flaw in the current
> implementation of PL/pgSQL that will not be resolved until we attract
> some new Postgres hackers who really care about upgrading PL/pgSQL.
>
> Currently, if you want to use an array, it has to be passed as a
> parameter, or come from an external table. You cannot declare an
> Array data type. Annoying, really.
If I replace the return_array allocations in the above example with this
line:
return_array := ''{ ''''test'''', ''''test 1'''', ''''test 2''''}'';
it _seems_ to work as expected, at least in 7.3b1., e.g.
test=> select array_dims(test_array) from test_array();
array_dims
[1:3]
(1 row)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] replace null with 0 in subselect ?
On Wednesday 16 October 2002 12:07, Albrecht Berger wrote: > Hello, > I have a statement like this : > > INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2); > > This works fine if the subselect returns a value, but if it returns > null there is a problem. In this case a 0 has to be inserted. > Is there any pg function which solves this problem ? > I know that oracle has a function but I didn't find > something similar in pg. A quick and dirty solution (untested): INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, (SELECT CASE WHEN (SELECT MAX(pos)+1 ) IS NULL THEN 0 ELSE (SELECT MAX(pos)+1 ) END) ) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 7.2 functions that return multiple result sets?
On Sunday 02 February 2003 22:45, mail.luckydigital.com wrote: > Can some one please confirm( with a plpgsql function example please ) a > postgres "7.2" function that can return multiple rows to the client. > > I've gone through the docs and can't find anything to support this -it > seems you can only have one return value or null. > > Yes i'm aware this it is possible in 7.3 - can someone please confirm its > not possible in 7.2 or provide me with an example of how to go about it. IIRC this is a new feature in 7.3, see: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=release.html "Allow functions to return multiple rows (table functions) (Joe)" Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Format in psql
On Monday 03 February 2003 11:54, Bhuvan A wrote: > Hi, > > I am using pgsql-7.2.3. Can i able to format the output of a SELECT sql in > psql as perl format? > > Something like, > +--++ > | work_desc_id | short_desc | > +--++ > |1 | Short description line 1 | > | > | | Short description line 2 | > | | Short description line 3 | > | | Short description line n | > +--++ 'fraid I don't understand what you mean by "perl" format. You can get format in the style above by executing \pset border 2 in psql. (SQL*Plus fans should issue \pset border 0 and recompile psql without readline support to simulate an Oracle environment ;-) Place this setting in your .psqlrc file to make it permanent. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting rid of accents..
On Tuesday 27 May 2003 22:55, [EMAIL PROTECTED] wrote:
> Is there any easy way for converting accented text to
> closest text without accents in postgresql ?
>
> eg:
>
> BÂLÂ MORGHÂB to BALA MORGHAB
Have you looked at to_ascii()? Something along the lines of
select to_ascii('Â', 'LATIN1')
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Dynamic views/permissions
On Friday 30 May 2003 21:31, Raj Mathur wrote: > I'm trying to permit users access to their own records in a database. > A sample would be: > > create table logins > ( > login char(8), > name char(32), > primary key (login) > ); > > When a login is added an SQL user is created simultaneously. Now I > want the user to be able to view her own data: > > create function userid() returns text as 'select current_user::text;' > language sql; > > create view loginview as select * from logins where logins.login=userid(); > > grant select on loginview to public; > [OR grant select on loginview to ...] > > However, a select * from loginview; doesn't return any rows. Is what > I'm trying not possible at all, or am I just trying to implement it > wrong? What happens if you define logins.login as VARCHAR instead of CHAR? See also: http://www.postgresql.org/docs/faqs/FAQ.html#4.14 Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Elegant SQL solution:
On Friday 06 June 2003 18:26, Chris Gamache wrote: > I could create a one-column table with values 1 - 12 in it, and select from > that table with a where clause matching "month". I could also create a view > "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST > be a more elegant way to do this. You probably need a pivot table (the one-column table with values 1 - 12). Oracle Magazine had a useful article on this subject (relevant for none-Oracle SQL too) a while back: http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html (registration probably required). Of course you could also use a set returning function a la: CREATE OR REPLACE FUNCTION months() RETURNS SETOF INT AS ' BEGIN FOR i IN 1..12 LOOP RETURN NEXT i; END LOOP; RETURN; END;' LANGUAGE 'plpgsql'; Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Delete duplicates
On Sunday 22 June 2003 11:15, Rudi Starcevic wrote:
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
(...)
> select a1.id
> from aap a1
> where id < ( SELECT max(id) FROM aap AS a2 )
> AND EXISTS
> (
> SELECT *
> FROM aap AS a2
> WHERE a1.keyword = a2.keyword
> )
How about (untested):
SELECT a1.id
FROM aap a1
WHERE id = (SELECT MAX(id) FROM aap a2
WHERE a2.keyword = a1.keyword)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] UPDATE table SET col = (SELECT ...)
On Wednesday 25 June 2003 11:00, Vangelis-Maria Tougia wrote: > Hi, > I found follwing email sent to you in internet. > I am interested in same issue myself. > Did you finally found out any more information about how to Update a column > of a table by selecting value from another table? Not sure if this has been answered previously, but here goes: > Hello, > > If I understand the SQL references I have read, UPDATEs are allowed to > pull data from SELECT statements, something like > > UPDATE table SET col1 = (SELECT val1 FROM table WHERE id = 34), >SET col2 = (SELECT val2 FROM table WHERE id = 34) ^^^ > WHERE id = 35; The second "SET" is not necessary or allowed and is causing the update to fail. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] plpgsql - how can I use TG_OP ?
On Wednesday 02 July 2003 18:49, Andreas Schmitz wrote: > Hello *, > > I have a litlle problem writing a plpgsql trigger function. I am in need to > get some interpretaion of the TG_OP within an IF clause to decide with > action will be taken. I tried it that way: > > IF (TG_OP=DELETE AND check_count > 1) THEN > (...) > What am I making wrong ? Not quoting? Try: IF (TG_OP=''DELETE'' AND check_count > 1) THEN Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] INSERT ... SELECT problem in Mysql
On Saturday 05 July 2003 00:06, Evil Azrael wrote: > Thanx for the fast answer. I already thought something about like > this and considered sending the question to the advocacy list instead > of the SQL list. I really like their docs since i´ve read about their > reasons against Foreign Keys *g* > > One more point in favor for PostgreSQL :) I've been here before, this thread: http://archives.postgresql.org/pgsql-sql/2003-04/msg00159.php might be of interest. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Optional join
On Thursday 14 August 2003 16:40, Slawek Jarosz wrote: > Hi, > > I trying to write a query that will join 2 tables. Here's the concept: > Table 1: table1, primary key pk1 > Table 2: table2, primary key pk2 > > One of the fields (f2) in table2 contains either the primary key of table1 > or a NULL value. So normally a pretty basic query: > > SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = > table1.pk1; > > BUT what I would like to do is show all records of Table 1 even if there is > no match in Table 2. Meaning that the reults could be table1... > table2.pk2 > table1... NULL > > Doable? You need an OUTER JOIN, see e.g. http://www.postgresql.org/docs/7.3/static/tutorial-join.html Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to join from two tables at once?
On Tuesday 26 August 2003 02:58, Joseph Shraibman wrote: > How can I join on one table with join conditions refering to two tables? > In this example p is missing an entry that corresponds to u. I want to > select from u and p, but have entries in u that don't have an entry in p. > The problem is I need to go through table a to get the corresponding value > in table p, and a LEFT JOIN only operates on two tables. The subselect > works, but in real life turns out to be a big performance drain. (...) > -- doesn't get 2, because there is no entry in p for it > SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid > and p.pkey = a.pkey; > > -- works, but uses a subselect > SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = > a.pkey) FROM u,a WHERE a.id = u.aid; > > --doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of > JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND > p.pkey = a.pkey WHERE a.id = u.aid; Try: SELECT u.uid, u.txt, p.val FROM u INNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.pkey=a.pkey AND p.uid=u.uid) Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Name the Parameters
On Saturday 06 September 2003 06:42, Kumar wrote: > Dear Gurus, > > Is it possible to name the IN parameters of the PLpgSQL Functions. Because > naming the parameter will ease coding. See the following function for > insert, having 10 parameters as a input. No, though there is an item on the TODO list IIRC. Using ALIAS FOR might make things easier though, something along the lines of: DECLARE address_id ALIAS FOR $1; address ALIAS FOR $2; ... BEGIN ... END; Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Conversion from Sybase ASA
On Tuesday 09 September 2003 09:06, Richard Sydney-Smith wrote: > In sybase I can use the following syntax: > > select 'T' as src,(if tbon is null or tbon < '1900/12/12' or tbon > > '1900/9/30' then 'U' else 'P' endif) as pres from ftrans > > this returns two columns: src = 'T' and pres which has either the value 'U' > or 'P' > > how would I express this in postgresql? "CASE": http://www.postgresql.org/docs/7.3/static/functions-conditional.html Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [postgres] Copy Timestamp NULL
On Thursday 09 October 2003 09:55, Danny Tramnitzke wrote: (...) > Also es geht darum, dass ich in eine Postgres Tabelle per Copy Daten laden > möchte. > Bei einer Spalte handelt es sich um Timestamp NULL ... In der Source-Datei > befinden sich in dieser Spalte ISO Timestamp Werte und NULL - Werte . > (Nicht jede Zeile besitzt einen Timestamp-wert) > > Die Source-Datei sieht prinzipiell so aus : > > 1|Hallo|17.0|1999-01-23 14:30:08.456234|usr01 > 2|Test|18.5||usr02 > > Die Spalte 4 ist somit vom Typ Timestamp. > > Allerdings kann ich diese Source-Datei nicht in die Postgres Tabelle per > Copy laden, da es an den NULL - Stellen zum Fehler kommt : Bad timestamp > external representation '' > > Wenn also eine "Lücke" gefunden wird, interpretiert Copy diese "Lücke" > nicht als Null-Wert und beschwert sich über das fehlerhafte Timestamp > Format. > > Es geht ebenfalls nicht, wenn ich an der entsprechenden Stelle NULL > schreibe. > > Was muss ich also angeben, damit ich solch eine Datei laden kann ? Ich nehme an, Du fuehrst in etwa folgendes aus: COPY x FROM '/pfad/zur/datei' DELIMITER '|'; Da musst Du dann in den Spalten mit einem NULL-Wert \N einfügen, also: 2|Test|18.5|\N|usr02 Das kann auch ein anderer Wert sein, z.B. COPY x FROM '/pfad/zur/datei' DELIMITER '|' NULL 'NULL'; um NULL schreiben zu koennen. Die relevante Stelle in der Dokumentation ist hier: http://www.postgresql.org/docs/7.3/static/sql-copy.html mfg Ian Barwick -- [EMAIL PROTECTED] http://www.postgresql.org/docs/faqs/FAQ_german.html http://sql-info.de/postgresql/de/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [postgres] Antwort von Microsoft auf Mail hier!
[to anyone reading this on the pgsql-sql list: for some reason mails to a postgres list at yahoogroups are being misdirected. I don't know why.] On Monday 13 October 2003 14:37, Alvar Freude wrote: > - -- Danny Tramnitzke <[EMAIL PROTECTED]> wrote: > > da ist wohl was zwischen MS und Yahoo im Busch ... > > die Frage ist, inwieweit das bekannt gegeben ist, ob das geheim abläuft > usw. > > Wenn da entsprechendes der Fall sein sollte, sehe ich das durchaus als > kleinen Skandal an: wenn ich Betreiber einer solchen Liste wäre, würde ich > schon gerne die Möglichkeit haben zu wissen, wer alles mitließt. Gut, dass > ich meinen eigenen Listen-Server betreibe ;-) Weiss nicht, ob da dunkle Machenschaften im Spiel sind ;-), aber auf jeden Fall gehen die Mails an [EMAIL PROTECTED] seit Freitag auch an die (englischsprachige) Liste pgsql-sql bei postgresql.org, was eindeutig fragwuerdig ist. Vielleicht waere es eine Idee, diese Liste bei postgresql.org hosten zu lassen? Das laesst sich relativ unbuerokratisch einrichten, auch andere nicht-englischsprachige Listen gibt es dort. (postgresql.org zieht gerade um, deshalb funktioniert zur Zeit gar nichts, ist aber sonst recht zuverlaessig). Ian Barwick [EMAIL PROTECTED] Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [postgres] PostgreSQL-DB auf Web-Schnittstelle bringen
On Saturday 18 October 2003 22:08, [EMAIL PROTECTED] wrote: > Hallo, Liste! Hallo, Struppi! > Welches Verfahren/Vorgehen würdet Ihr mir denn empfehlen, wenn ich eine > PostgreSQL- Datenbank auf eine Web-Schnittstelle bringen will? Fuer diese Aufgabe wuerde sich eignen z.B. phpPgAdmin: http://phppgadmin.sourceforge.net/ Ian Barwick [EMAIL PROTECTED] Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Compare strings which resembles each other
On Friday 21 November 2003 15:32, Oleg Bartunov wrote: > On Fri, 21 Nov 2003, Christoph Haller wrote: > > $PGSQLD/contrib/tsearch/README.tsearch > > is your friend. > > I think tsearch(2) isn't what Gulshan need. He needs fuzzy search, > there are several modules in contrib directory (metaphone, fuzzystrmatch). > Also, we have trgm module which uses trigram and has index support. Hi Oleg, do you have any more information on trgm? According to your website it isn't released. I am looking for something along those lines and might be able to assist, at least with testing and documentation. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 'show databases' in psql way?
On Mon, 1 Nov 2004 13:13:10 +0100, Erik Wasser <[EMAIL PROTECTED]> wrote: > Hi list, > > how can I list the databases in a postgresish way? I know about the '-l' > switch of 'psql' but is there a DBI/SQL-query way? I don't want to call > an external program only to list the databases. I've googled about this > problem but I only found the '-l'-way to this this. > > Ideas? Solutions? Start psql with the -E switch, and it shows the SQL used to generate the output from psql's slash commands. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] oracle v$session equivalent in postgresql
On Fri, 5 Nov 2004 08:27:58 -0700, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
>
> I'm looking for the equivalent in postgresql to the v$session variables in
> Oracle. In particular, I need to convert the following statement:
>
> select s.program, s.machine into :New.app_name,:New.comp_name from v$session
> s where s.audsid=userenv('SESSIONID');
You want basically this information then:
SQL> select s.program, s.machine from v$session s where
s.audsid=userenv('SESSIONID');
PROGRAM
MACHINE
[EMAIL PROTECTED] (TNS V1-V3)
linux
There's no direct equivalent of v$session in PostgreSQL, and I don't
know any way of determining the client program name (AFAIK). More
information about monitoring user activity can be found e.g. here:
http://www.postgresql.org/docs/current/static/monitoring.html
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Comparing Dates
On Thu, 18 Nov 2004 15:01:58 -0600, Nick Peters <[EMAIL PROTECTED]> wrote: > Hey, > > I am trying to compare dates in a sql statement. this is what i have tried: > > SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND > transtype='Sale'; SELECT * FROM transactions WHERE shippingdate> '2004-06-08' AND transtype='Sale'; Ian Barwick ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004 10:30:55 +0530, Kenneth Gonsalves
<[EMAIL PROTECTED]> wrote:
> hi,
>
> from mysql:
>
> field enum('a','b','c') default null,
>
> i translated this as:
>
> field varchar(2) check (field in (null,'a','b','c')),
>
> is it necessary to put the 'null' in the check condition? if not will pg
> permit you to enter a null value in the field?
No, and yes:
create table consttest (field varchar(2) check (field in ('a','b','c')));
insert into consttest values (null);
Note this does not emulate the MySQL ENUM datatype precisely,
because it's possible to reference the ENUM fields by index value
too.
(Oddly enough, putting the NULL in the CHECK constraint seems
to make the constraint worthless:
test=> create table consttest (field varchar(2) check (field in
(null, 'a','b','c')));
CREATE TABLE
test=> insert into consttest values ('xx');
INSERT 408080 1
test=> SELECT * from consttest ;
field
---
xx
(1 row)
Not sure what logic is driving this).
Ian Barwick
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<[EMAIL PROTECTED]> wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttest (field varchar(2) check (field in
> > (null, 'a','b','c')));
> > CREATE TABLE
> > test=> insert into consttest values ('xx');
> > INSERT 408080 1
> > test=> SELECT * from consttest ;
> > field
> > ---
> > xx
> > (1 row)
> >
> > Not sure what logic is driving this).
>
> The way NULL is handled in IN (because it's effectively an equality
> comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
> never return false and constraints are satisified unless the search
> condition returns false for some row. I think this means you need the
> more verbose (field is null or field in ('a','b','c'))
This works as expected, although for constraints the nullness
of the column is better off handled by applying NOT NULL if
necessary.
What I still don't quite understand is why IN in a CHECK context is
handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
This could be a bit of a gotcha for anyone constructing a constraint
similar to the original poster's and not realising it has no effect.
Ian Barwick
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Postgres performance
On Wed, 02 Mar 2005 09:00:14 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote: (...) > The reason PostgreSQL is slower is because it (and by extension the team > behind it) cares about your data. > > Here's a list of the things MySQL will gladly do wrong: > > http://sql-info.de/mysql/gotchas.html Leaving MySQL or other databases out of the equation for the moment: the above site is a purely dynamic website (i.e. no static files, not even images) driven by a PostgreSQL backend. There are several issues with the underlying application (a DIY hack job ;-) which mean it isn't as fast as it could be. However, although I haven't been able to run comparisions with other RDBMSs I find it hard to imagine where significant speed gains could be made at the database end, especially if stored procedures are not available (any raw speed increase could well be eaten up by the need to implement several critical functions in the application). Recently I added a function (for another site on the same server, running from the same database) to generate a blog-style calendar for a given month to show on which days an article was written. Despite involving a three-table join with a longish list of join conditions it proved to be jaw-droppingly fast (a few milliseconds, fast enough not to have to cache the result anywhere, which is what I was originally expecting to have to do) and as an added bonus returns the weekday expressed as an integer, so all the application has to do is a little formatting to produce the end result. I've also run a PostgreSQL-based multi-thousand page site (with a simpler structure) without any complaints speedwise; and when one of the disks died very nastily during an intensive write operation (software raid on dodgy hardware) I was even able to rsync the database files direct from the surviving disk over to a backup server and restart PostgreSQL there straight off, without any evident problems. (Disclaimer: it was an emergency, and the data was non-critical; nevertheless I never found any evidence of corruption). Ian Barwick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query issue/8.0.1/Serendipity
>On Tue, 08 Mar 2005 10:37:51 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Larry Rosenman writes: > > I have an 8.0.1 server running the Blogging software serendipity, and the > > following query fails with "relation e not defined", but it is on the first > > line: > > > "SELECT timestamp FROM serendipity_entries e, serendipity_category c, > > serendipity_entrycat ec LEFT OUTER JOIN serendipity_entryproperties > > ep_cache_extended ON (e.id = ep_cache_extended.entryid AND > > ep_cache_extended.property = 'ep_cache_extended') ... > > Broken SQL that's only ever been tested on MySQL. > > Last I heard, MySQL treated this sort of construct as joining > left-to-right, ie, > > FROM e CROSS JOIN c CROSS JOIN ec LEFT JOIN ... > > in which case the left argument of the LEFT JOIN already contains > e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately > for MySQL users everywhere, this is expressly contrary to the SQL spec: > per spec, JOIN binds more tightly than commas in the FROM-list do. > > (Is this on the mysql gotchas page?) Nope, although as my plans for the week involve evaluating Serendipity using PostgreSQL I'll look into it. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Plpgsql: Assign regular expression match to variable
2009/9/1, Leif B. Kristensen : > In Plpgsql, I've got this problem of how to assign an integer extracted > from a regex to a variable. My approach so far feels kludgy: > > -- extract ^#(\d+) from txt > IF txt SIMILAR TO E'#\\d+%' THEN > my_int := SUBSTR(SUBSTRING(txt, E'#\\d+'), 2, > LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER; > -- strip ^#\d+ from text > my_txt := REGEXP_REPLACE(txt, E'^#\\d+ ', ''); > END IF; > > What I'd like to do is something like this: > > my_int := MATCH(txt, '^#(\d+)')::INTEGER; > > which would assign the integer atom (\d+) to my_int. This seems to do what you want: my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1]; Ian Barwick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to do this query
2010/1/9 Wes James : > I have two tables: > > students > stu_name > schols_selected > > scholarships > schol_name > short_name > > schols_selected is made up of scholarships the students have selected, > the field content will look like schol1:schol2:schol3 > > I need a select that does something like this > > select schol_name, short_name, stu_name, schols_selected > from scholarships, students > where short_name is in schols_selected > > I have tried: > > where short_name like '%' || schols_selected || '%' > > but i'm not sure how to include a field result in between like operators. Theoretically this construction is correct, but as the LIKE pattern is probably longer than the contents of the field you are operating on, it won't work in the way you intend it to. > how do I get a list of scholarships based one which student selected > it where the selected result is a string of colon seperated options? I'm not quite sure what you mean here, but in order to be able to match scholarships selected to an individual student given your table structure, you could try turning the fields round, e.g. WHERE schols_selected LIKE '%' || short_name || '%' However this is a lousy way of doing things and will cause performance problems with a non-trivial amount of data. (An additional table is what would would help you here). HTH Ian Barwick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] rename primary key
2010/1/15 Seb :
> Hi,
>
> Is it possible to rename a primary key? I learnt that to rename foreign
> keys one has to drop it and recreate with the new name, or add a new new
> and drop the old one. What is the approach for primary keys? Thanks.
Is this what you mean?
psql (8.4.2)
You are now connected to database "test".
test=# CREATE TABLE foo (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=# \d foo
Table "public.foo"
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
test=# ALTER INDEX foo_pkey RENAME to bar_fkey;
ALTER INDEX
test=# \d foo
Table "public.foo"
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"bar_fkey" PRIMARY KEY, btree (id)
test=#
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ask about epoch query with datetime
2009/5/7 haries fajar nugroho :
> Hi All,
>
> my institution wants me to migrate from old postgresql to new
> postgresql, but when i test one of the query it failed at query :
> select date_part('epoch',datetime('2009-04-20
> 17:08:01')-datetime('2009-04-20 17:04:24'))::int;
> that query runs well in previous postgresql (PostgreSQL 7.1.3). The
> error message is ERROR: function datetime("unknown") does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> If i read the error message, it means the function has changed or
> something. May I know the latest function so my query can run well in
> my PostgreSQL 8.1.11 ?
Try something like:
select date_part('epoch','2009-04-20 17:08:01'::TIMESTAMP -
'2009-04-20 17:04:24'::TIMESTAMP)::int;
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
