Re: [SQL] SELECT with Function
Dear Marcin Thanks for your reply. My created function running well as exposed by you in ttt() function and this is no problem. I need extend this query. See: TableA prod_idnumeric(10) prod_name varchar(40) ... My function, as your ttt(), return 3 values and value prod_id from TableA must be parameter to function. I like obtain as result: prod_id, prod_name, v1, v2, v3 The first 2 columns is from TableA and v1,v2,v3 is from function. How create a query that return this row? Or it is impossible? I have tried with UNION and JOIN without success :-( Excuse me: you is polish? From Poland? I brazilian descendant polish. Thanks Paulo Nievierowski On Fri, 01 Oct 2004 18:46:45 +0200, Marcin Piotr Grondecki wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Paulo Nievierowski wrote: > | PS: Excuses my poor english. > Your english is excellent. MY is poor!! > > | I create plpgsql function "myfunc(integer)" thats returns of type > | record with values from table X (the function decides what record > must > | be select). The parameter is an keyprod existing in table X and in > | table A. > | > | It run well sending in psql: > | SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 > numeric); > | > | The problem is: > | I need return data from table A (this have column 'keyprod'). This > | value (keyprod) must be passed as parameter to myfunc(). The > result > | must be a union of columns selected from table A and result of > | myfunc(). > | > | How to create this query? It's possible? > Yep. > Look at this: > > drop table dupa cascade; > create table dupa (a int, b int, c int); > insert into dupa (a, b, c) values (1, 2, 3); > insert into dupa (a, b, c) values (2, 3, 4); > > create or replace function ttt(int) returns record as ' > declare > ~r record; > begin > ~select * into r from dupa where ($1=a); > ~return r; > end; > ' language 'plpgsql'; > > And then: > > ojciec=# select * from ttt(1) as foo(aa int, bb int, cc int); > ~ aa | bb | cc > - ++ > ~ 1 | 2 | 3 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SQL confusion
I'm trying to figure out how to do a particular query, and I'm beating my head against a wall. Here's my situation: I'm running postgres 7.3.2 on linux, and making my requests from Perl scripts using DBD::Pg. My table structure is as follows (irrelevant cols removed) CREATE TABLE name ( namecounter integer NOT NULL, firstmiddle character varying(64) NOT NULL, lastname character varying(64) NOT NULL, birthdate date, hh smallint, famnu integer, ); Each row represents a person with a unique namecounter. Families share a famnu, and usually one person in a family is marked as head of household (hh>0), with everyone else hh=0. However, there are a few families with nobody marked as hh, and I'd like to elect one by age. The query I'm trying to do is to pull one person from each household, either the head of household if available, or the eldest if not. I want them sorted by last name, so I'd prefer to find them all in one query, no matter how ugly and nested it has to be. I can pull the list with hh>0 easily enough, but I'm not sure how to pull out the others. I realize that this could be done through some looping in the Perl script, but I'd like to avoid pulling the whole list into memory in case the list gets long. My preference is to just handle one record at a time in Perl if possible. Help? Andrew Ward [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL confusion
On Sat, Oct 09, 2004 at 01:39:45PM -0700, Andrew Ward wrote: > > I'm running postgres 7.3.2 on linux, and making my > requests from Perl scripts using DBD::Pg. My table > structure is as follows (irrelevant cols removed) > > CREATE TABLE name ( > namecounter integer NOT NULL, > firstmiddle character varying(64) NOT NULL, > lastname character varying(64) NOT NULL, > birthdate date, > hh smallint, > famnu integer, > ); This may not be the fastest query, but it should be quite comprehensible. Lightly tested only on 7.4.5 (do you know why you're not using 7.4.x?). SELECT namecounter -- and whatever other columns you need FROM name outername -- alias so it's clear what the joins do WHERE hh = 1 -- simple case: head of family OR ( hh = 0 -- not a head AND NOT EXISTS ( -- there is no head of this family SELECT namecounter FROM name in1 WHERE hh = 1 AND in1.famnu = outername.famnu) AND birthdate = ( -- this person is as old as the oldest person SELECT MIN(birthdate) FROM name in2 WHERE in2.famnu = outername.famnu) ); Richard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL confusion
On Sat, Oct 09, 2004 at 01:39:45PM -0700, Andrew Ward wrote: > > CREATE TABLE name ( > namecounter integer NOT NULL, > firstmiddle character varying(64) NOT NULL, > lastname character varying(64) NOT NULL, > birthdate date, > hh smallint, > famnu integer, > ); > > Each row represents a person with a unique > namecounter. Families share a famnu, and usually one > person in a family is marked as head of household > (hh>0), with everyone else hh=0. However, there are a > few families with nobody marked as hh, and I'd like to > elect one by age. The query I'm trying to do is to > pull one person from each household, either the head > of household if available, or the eldest if not. I > want them sorted by last name, so I'd prefer to find > them all in one query, no matter how ugly and nested > it has to be. This should be close to what you need: SELECT DISTINCT ON (lastname, famnu) * FROM name ORDER BY lastname, famnu, COALESCE(hh, 0) DESC, birthdate; The order of the fields in the ORDER BY clause is important, so if you want to sort the results by some other criteria then you might need to do it with a subselect. I used COALESCE on the hh field because a descending sort puts NULL values ahead of non-NULL values -- without COALESCE a person with hh=1 won't be recognized as the head of household if another member of the family has hh=NULL. You might need to modify the query to handle NULL birthdates or to add other tiebreakers. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[SQL] How do FKs work?
Got a problem here, and this is a new area for me ... analyzing FKs and improving their performance :) Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it never comes back ... or, at lesat, takes a *very* long time ... If I do a count(1) for that #, there are 1639 rows ... Now, 'ON DELETE' is set to 'NO ACTION', so my *guess* is that all it does is a 'SELECT FROM table WHERE field = value' on the referenced table, to make sure it exists ... Is this correct? So, its effectively having to do 3278 "SELECTS" against the REFERENCED table? (two fields have contraints on them, 1639 rows to be deleted) ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How do FKs work?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Have a table with two FKs on it ... 2 different fields in the table point > to the same field in another table ... > When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it > never comes back ... or, at lesat, takes a *very* long time ... Do you have indexes on the referencing columns? Are they exactly the same datatype as the referenced column? You can get really awful plans for the FK-checking queries if not. regards, tom lane ---(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] How do FKs work?
On Sat, 9 Oct 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it never comes back ... or, at lesat, takes a *very* long time ... Do you have indexes on the referencing columns? Are they exactly the same datatype as the referenced column? You can get really awful plans for the FK-checking queries if not. Yup, that was my first thought ... running SELECT's joining the two tables on the FK fields shows indices being used, and fast times ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How do FKs work?
On thing I failed to note here, that is probably critical ... its a 7.3 database ... On Sat, 9 Oct 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it never comes back ... or, at lesat, takes a *very* long time ... Do you have indexes on the referencing columns? Are they exactly the same datatype as the referenced column? You can get really awful plans for the FK-checking queries if not. regards, tom lane Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
