[SQL] Record type in sql
Hello, I have a little problem I want to declare a type record for later use like that create type record_structure1 as (id int2, nume text); that is ok! next in a function I want to use something like that: select * from table as record_structure1 ? instead of writing select * from table as t1(id int2, nume text); is this possible? Thank You, Adrian Din -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(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] Record type in sql
Yo can do s.t. similar, if the order in which you select the columns corresponds to the order your type is defined. So you'd gonna use: select intcol, textcol from table; > -Original Message- > From: Din Adrian [mailto:[EMAIL PROTECTED] > Sent: Montag, 17. Jänner 2005 13:31 > To: [email protected] > Subject: [SQL] Record type in sql > > > Hello, > I have a little problem > I want to declare a type record for later use like that > > create type record_structure1 as (id int2, nume text); > that is ok! > > next in a function I want to use something like that: > > select * from table as record_structure1 ? > instead of writing > select * from table as t1(id int2, nume text); > is this possible? > > Thank You, > Adrian Din > > -- > Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inserting or Deleting conditionally
Hi, coming from imperative programming paradigma, I'm currently trying to express something like that in _SQL_: It should be atomic and like an API for a user. I'm therefore writing functions: CRETE FUNC... c := SELECT x,y,z FROM table_a WHERE... IF COUNT(c)=1 then INSERT / DELETE ... WHERE x = c.x ENDIF LANGUAGE SQL; How is this correctly formulated? Or is there no other way than PL/xxx? Thanks ---(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] Inserting or Deleting conditionally
KÖPFERL Robert wrote: Hi, coming from imperative programming paradigma, I'm currently trying to express something like that in _SQL_: SQL is not an imperative language - each SQL statement is an expression to be evaluated. It should be atomic and like an API for a user. I'm therefore writing functions: CRETE FUNC... c := SELECT x,y,z FROM table_a WHERE... IF COUNT(c)=1 then INSERT / DELETE ... WHERE x = c.x ENDIF Even if this was valid syntax, you've just made your query N times more expensive. Try something like: INSERT ... WHERE x IN (SELECT x FROM table_a WHERE ...) or similar. How is this correctly formulated? Or is there no other way than PL/xxx? Sounds like you want a middle-ware layer to provide an API for your applications. Plenty of options, but it's difficult for people to make suggestions without knowing more about your situation. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plpgsql and for with integer loopvar error
I have a single column result that I want to loop through using an index. I also want to jump ahead in the result using the index (below: i:=i+cluster_cnt-1;) . However, I'm getting the following error. ERROR: syntax error at or near "SELECT" at character 9 CONTEXT: PL/pgSQL function "gets_nooky" line 14 at for with integer loopvar Here is my function ... CREATE FUNCTION gets_nooky() returns numeric AS ' DECLARE i integer; gt1cnt record; gt1 record; cluster record; cluster_cnt integer; slocus integer; minmax record; BEGIN SELECT INTO gt1 * FROM cgt1; SELECT count(*) INTO gt1cnt FROM cgt1; FOR i IN 1 .. SELECT count(*) FROM cgt1 LOOP SELECT INTO cluster DISTINCT qry FROM seeds2 WHERE seed IN ( SELECT qry FROM seeds2 WHERE seed=gt1.seed[i]); slocus := i; SELECT INTO minmax min(qle) AS mqle, max(que) AS mque FROM seeds2 WHERE seed IN (SELECT * FROM cluster); UPDATE locus SET locus=slocus, lle=minmax.mqle, lue=minmax.mque WHERE id IN ( SELECT * FROM cluster ); SELECT INTO cluster_cnt count(*) FROM cluster; i:=i+cluster_cnt-1; DELETE FROM cluster; END LOOP; return i; END; ' language 'plpgsql'; Any help is much appreciated! Thanks, Ari ---(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
[SQL] query configuration for validate empty quote to zero
We just changed the postgresql server and got some problem with sql command in our old server we can exexute this query: SELECT * FROM product WHERE prdtnumber = '' field prdtnumber data type is integer and the empty quote somehow can be assumed into 0 (zero) Now, if I use the same query it return an error: invalid input syntax for integer: "" Anyone know what's the problem?? Thanks Yudie
Re: [SQL] query configuration for validate empty quote to zero
"Yudie" <[EMAIL PROTECTED]> writes: > Now, if I use the same query it return an error: > invalid input syntax for integer: "" > Anyone know what's the problem?? None. That's an intentional change. The fact that it ever accepted '' as meaning 0 was a bug. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql and for with integer loopvar error
On Sat, 15 Jan 2005, Ari Kahn wrote: > CREATE FUNCTION gets_nooky() returns numeric AS > ' > DECLARE > i integer; > gt1cnt record; > gt1 record; > cluster record; > cluster_cnt integer; > slocus integer; > minmax record; > > BEGIN > SELECT INTO gt1 * FROM cgt1; > SELECT count(*) INTO gt1cnt FROM cgt1; > > FOR i IN 1 .. SELECT count(*) FROM cgt1 I think either of (SELECT count(*) FROM cgt1) or count(*) from cgt1 should work in practice (I personally like the former because that's the scalar subquery syntax), however, since you're getting the count into gt1cnt, why not just use the variable? ---(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] query configuration for validate empty quote to zero
The problem is not stop there, I had a table that can't be updated since then with this simply command: update prdttable set webinclude = '0' where prdtnumber = '6002' Again, the errors (invalid input syntax for integer: "") apears. Then I found this temporary solution to make replica to this record to another table in sense of update the value in the new table. 1. Create table prdt_problem as select * form prdttable where prdtnumber = '6002'; 2. Update prdt_problem set webinclude = '0' where prdtnumber = '6002'; 3. Delete from prdttable where prdtnumber = '6002'; 4. Insert into prdttable select * from prdt_problem How to fix this bug? > None. That's an intentional change. The fact that it ever accepted > '' as meaning 0 was a bug. > > regards, tom lane > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] "How do I ..." SQL question
Hi there: I have a "How do I..." SQL question regarding selecting distinct values from a field not included in an aggregated query when LIMIT is in effect, illustrated by the following example: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 kim2004 1 joe2005 Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. -Bob ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] "How do I ..." SQL question
Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: Is this : SELECT DISTINCT place FROM a,( SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1 ) as foo WHERE name=foo.name AND year=foo.year Problem with this approach is that you'll have to run the query twice, one to get the hitlist by user, one for the places... SELECT DISTINCT place FROM a ; place --- south west east Note that the place north does not appear in the last result because north was only visited by bob in 2005 and kim in 2004, records which are not included in the limited result. Any help appreciated. -Bob ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
