[SQL] set return function is returning a single record, multiple times,how can i get all the records in the table( description inside )
Hi Please spare some time to provide a solution for the described problem : I am using set returning functions to return all the records from a table named pss , But what I am getting is the first record is returned as many times , the number of records present in the rank_master: I am giving a detailed description below please check it out 1) The following query creates pss table: create table pss( name varchar(20), num integer, phno integer ); 2) insert three records in to pss : insert into pss values(‘penchal’,1,420); insert into pss values(‘joe’,2,421); insert into pss values(‘ali’,3,422); 3) create an user defines type of variable named structrankmaster2 ( something like a structure to hold a record ) : create type Structrankmaster2 as (name varchar(20), num integer, phno integer); 4) The following is the function that retrieves the records from pss : CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 LANGUAGE 'plpgsql' AS' DECLARE rowdata pss%rowtype; BEGIN for i in 1..3 loop select * into rowdata from pss ; return next rowdata ; end loop; return; end'; 5) now call the function from command prompt: Select * from ftoc9(); 6) the following is the output that I am getting ( i.e the first row repeated 3 times ) : name | num | phno -+-+-- penchal | 1 | 420 penchal | 1 | 420 penchal | 1 | 420 (3 rows) 7) what exactly I should be getting is : name | num | phno -+-+-- penchal | 1 | 420 joe | 2 | 421 penchal | 3 | 422 (3 rows) Please provide a solution for this so that I can get name | num | phno -+-+-- penchal | 1 | 420 joe | 2 | 421 penchal | 3 | 422 (3 rows) Thanks & regards Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Re: [SQL] set return function is returning a single record, multiple times,how can i get all the records in the table( description inside )
am 28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes: > 4) The following is the function that retrieves the records from pss : > > CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 > LANGUAGE 'plpgsql' > > AS' DECLARE > rowdata pss%rowtype; > BEGIN for i in 1..3 loop > select * into rowdata from pss ; > return next rowdata ; > end loop; > return; > end'; Your loop is wrong, for i in 1..3 select... and then returns the first record. Change this to: BEGIN .. for rowdata in select * from pss ; return next rowdata ; end loop; .. END; *untestet* HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] set return function is returning a single record, multiple
CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 LANGUAGE 'plpgsql' AS' DECLARE rowdata pss%rowtype; BEGIN for i in 1..3 loop select * into rowdata from pss ; return next rowdata ; end loop; return; end'; The query should be outside the loop, otherwise you are re-running the query each time :-) Alex ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Outer joins?
Hello! I am a longtime postgres user (started around 10 years ago), however, as for some years I've been using it mostly as administrator. Now that I have started a project and doing some SQL, I've come up something I don't believe is right. Maybe I am too rusty on my SQL - if so, please forgive me, but I checked it and my reasoning seemed ok to me. I am trying to do simple self-joins. The table structure is: object_values == obj_id att_id value namely, each object can have arbitrary number of attributes each of them with a value. What I want, is a simple table of objects with some of their specific attributes, the result should be in form: obj_id1 o1att1_value o1att2_value o1att3_value obj_id2 o2att1_value o2att2_value o2att3_value ... Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in that grid point. So, I thought some nested outer joins should be OK? SELECT OV.obj_id AS obj_id, OV.value AS NAME, ov1.value AS DESCRIPTION, ov2.value AS ICON FROM object_values OV LEFT JOIN object_values ov1 USING(obj_id) LEFT JOIN object_values ov2 USING(obj_id) WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; So, I figured this should get me all objects that have atttribute 7 defined, regardless of whether the other attributes exist for them? However, for some reason PG8.1 is giving me something like an INNER join on this query - namely ONLY rows where ALL the attributes ARE present. Am I doing something wrong? As I said my SQL is rusty, but this looked pretty straightforward to me... Thanks in advance, Emils ---(end of broadcast)--- TIP 1: 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] Outer joins?
On Fri, 28 Apr 2006, Emils wrote: > I am trying to do simple self-joins. > > The table structure is: > > object_values > == > obj_id > att_id > value > > namely, each object can have arbitrary number of attributes each of > them with a value. > > What I want, is a simple table of objects with some of their specific > attributes, the result should be in form: > > obj_id1 o1att1_value o1att2_value o1att3_value > obj_id2 o2att1_value o2att2_value o2att3_value > ... > > Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in > that grid point. > > So, I thought some nested outer joins should be OK? > > SELECT > OV.obj_id AS obj_id, > OV.value AS NAME, > ov1.value AS DESCRIPTION, > ov2.value AS ICON > FROM > object_values OV LEFT JOIN object_values ov1 USING(obj_id) > LEFT JOIN object_values ov2 USING(obj_id) > WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; AFAIK, effectively first the join happens then the where filter. So, imagine the output of the joins without any where clause and then apply the where clause as a filter upon that. Even if you got NULL extended rows, you'd filter them out because the ov1.att_id and ov2.att_id tests would filter them out. In addition, you won't actually get NULL extended rows I think, because there will always be at least one row with matching obj_id (the one from ov that's being worked on). I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Another way of doing the same thing is using subselects in from to filter the right hand tables you wish to join. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] set return function is returning a single record,
On Fri, 2006-04-28 at 12:56 +0200, A. Kretschmer wrote: > am 28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes: > > 4) The following is the function that retrieves the records from pss : > > > > CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 > > LANGUAGE 'plpgsql' > > > > AS' DECLARE > > rowdata pss%rowtype; > > BEGIN for i in 1..3 loop > > select * into rowdata from pss ; > > return next rowdata ; > > end loop; > > return; > > end'; > > Your loop is wrong, for i in 1..3 select... and then returns the first > record. > > > Change this to: > > BEGIN > .. > for rowdata in select * from pss ; > return next rowdata ; > end loop; > .. > END; > > *untestet* If you meant to return the first 3 records, then: ... begin for rowdata in select * from pss limit 3 loop return next rowdata ; end loop; return; end'; You can also return a SETOF pss without creating the structrankmaster2 type. If this is actually all you are after, and not just a simplified example then you could also use this (also not tested): CREATE FUNCTION ftoc9() RETURNS SETOF pss AS $$ SELECT * FROM pss LIMIT 3; $$ LANGUAGE SQL; If you do use LIMIT, then ORDER BY might also be needed as well. Ross ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Outer joins?
Emils <[EMAIL PROTECTED]> writes: > The table structure is: > object_values > == > obj_id > att_id > value > namely, each object can have arbitrary number of attributes each of > them with a value. > What I want, is a simple table of objects with some of their specific > attributes, the result should be in form: > obj_id1 o1att1_value o1att2_value o1att3_value > obj_id2 o2att1_value o2att2_value o2att3_value > ... This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] LinkedList
On Thu, 2006-27-04 at 22:58 -0500, Ben K. wrote: > > I have a table that I created that implements a linked list. I am not an > > expert SQL developer and was wondering if there are known ways to traverse > > the linked lists. Any information that can point me in the direction to > > figure this out would be appreciated. The table contains many linked lists > > based upon the head of the list and I need to extract all of the nodes that > > make up a list. The lists are simple with a item and a link to the history > > item so it goes kind of like: > > It may not be exactly suitable, but this one does only traversal (assuming > the list is not clsoed) > > create table linkedlist(prevnode int, nextnode int, val int); > -- HEAD > insert into linkedlist values(null,1,0); > insert into linkedlist values(1,2,10); > insert into linkedlist values(2,3,20); > insert into linkedlist values(3,4,30); > insert into linkedlist values(4,5,40); > -- TAIL > insert into linkedlist values(5,null,50); > > -- TRAVERSE > begin; > declare mc cursor for select * from linkedlist order by nextnode; > fetch 1 from mc; > fetch 1 from mc; > ... > close mc; > commit; > > which is nothing more than, > select * from linkedlist order by nextnode; > > > Regards, > > Ben K. > Developer > http://benix.tamu.edu Bad example of a double linked list, you also need an id for the current node and the values of prevnode and nextnode do not need to be ordered or contiguous as the example shows. create table linkedlist(node int,prevnode int, nextnode int, val int); insert into linkedlist values(1,null,2,0); insert into linkedlist values(2,1,3,10); insert into linkedlist values(3,2,4,30); insert into linkedlist values(4,3,5,20); insert into linkedlist values(5,4,6,40); insert into linkedlist values(6,5,null,50); If we now wanted to reorder an item in the set you need make some updates in a block, which I have not done before but should be something like this: Move node 4 between 2 and 3 so that the values from head to tail are ordered. BEGIN update linkedlist set prevnode = '2',nextnode = '3' where node = '4'; update linkedlist set nextnode = '4' where node = '2'; update linkedlist set prevnode = '4' where node = '3'; COMMIT I have never done linked lists in SQL but have done a lot of work with bidirectional multi-dimensional linked lists in the past in C and other programming languages. The concept is the same. A single linked list would be easier, but can only be traversed in one direction : create table linkedlist(node int,nextnode int, val int); insert into linkedlist values(1,2,0); insert into linkedlist values(2,3,10); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,20); insert into linkedlist values(5,6,40); insert into linkedlist values(6,null,50); Again to order the val from head to tail: BEGIN update linkedlist set nextnode = '3' where node = '4'; update linkedlist set nextnode = '4' where node = '2'; COMMIT ---(end of broadcast)--- TIP 1: 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] Slightly confused error message
Hi, I just stumbled over a slightly confused error message: mydb=# select count(*),coverage_area from myschema.streets except select cd as coverage_area from countryref.disks group by streets.coverage_area; ERROR: column "streets.coverage_area" must appear in the GROUP BY clause or be used in an aggregate function As the query looks, streets.coverage_area is actually used in the GROUP BY. I know how to fix the query, but I wonder whether it is worth the effort to try improving the error message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slightly confused error message
Markus Schaber <[EMAIL PROTECTED]> writes: > I just stumbled over a slightly confused error message: > mydb=# select count(*),coverage_area from myschema.streets except select > cd as coverage_area from countryref.disks group by streets.coverage_area; > ERROR: column "streets.coverage_area" must appear in the GROUP BY > clause or be used in an aggregate function > As the query looks, streets.coverage_area is actually used in the GROUP BY. The complaint is 100% accurate; the subquery that it's unhappy about is select count(*),coverage_area from myschema.streets which is an aggregating query, but coverage_area is being used outside an aggregate without having been grouped by. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Slightly confused error message
Hi, Tom, Tom Lane wrote: >>I just stumbled over a slightly confused error message: > >>mydb=# select count(*),coverage_area from myschema.streets except select >>cd as coverage_area from countryref.disks group by streets.coverage_area; >>ERROR: column "streets.coverage_area" must appear in the GROUP BY >>clause or be used in an aggregate function > >>As the query looks, streets.coverage_area is actually used in the GROUP BY. > > The complaint is 100% accurate; I know that, and won't deny. > the subquery that it's unhappy about is > > select count(*),coverage_area from myschema.streets > > which is an aggregating query, but coverage_area is being used outside > an aggregate without having been grouped by. Yes, and my question is whether it is easy and worth the effort. to add that information (about the acutally offending subquery) to the message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Slightly confused error message
On Fri, 28 Apr 2006 12:07:04 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Markus Schaber <[EMAIL PROTECTED]> writes: >> I just stumbled over a slightly confused error message: > >> mydb=# select count(*),coverage_area from myschema.streets except select >> cd as coverage_area from countryref.disks group by > streets.coverage_area; >> ERROR: column "streets.coverage_area" must appear in the GROUP BY >> clause or be used in an aggregate function > >> As the query looks, streets.coverage_area is actually used in the GROUP > BY. > > The complaint is 100% accurate; the subquery that it's unhappy about is > > select count(*),coverage_area from myschema.streets > > which is an aggregating query, but coverage_area is being used outside > an aggregate without having been grouped by. I see lack of parenthesis in the sub-query: select count(*),coverage_area from myschema.streets except (select cd as coverage_area from countryref.disks) group by streets.coverage_area; -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slightly confused error message
Markus Schaber <[EMAIL PROTECTED]> writes: > Yes, and my question is whether it is easy and worth the effort. to add > that information (about the acutally offending subquery) to the message. I'm not sure about localizing the subquery per se, but it might be possible to add a syntax pointer to the specific variable occurrence that it's complaining about. That would help at least somewhat in cases like this. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] LinkedList
On Fri, 28 Apr 2006, Guy Fraser wrote: -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); Bad example of a double linked list, you also need an id for the current node and the values of prevnode and nextnode do not need to be ordered or contiguous as the example shows. Wow. Interesting... I am willing to be corrected, but to me the "node" field seems redundant, since it does not add any information. (Since each item in the list is already uniquely identifiable without the "node".) Certainly so, for traversing, which was the OP's intention. It may save some steps in case of other operations but at the expense of one more field. Please see below. create table linkedlist(node int,prevnode int, nextnode int, val int); insert into linkedlist values(1,null,2,0); insert into linkedlist values(2,1,3,10); insert into linkedlist values(3,2,4,30); insert into linkedlist values(4,3,5,20); insert into linkedlist values(5,4,6,40); insert into linkedlist values(6,5,null,50); If we now wanted to reorder an item in the set you need make some updates in a block, which I have not done before but should be something like this: Move node 4 between 2 and 3 so that the values from head to tail are ordered. update linkedlist set prevnode = '2',nextnode = '3' where node = '4'; update linkedlist set nextnode = '4' where node = '2'; update linkedlist set prevnode = '4' where node = '3'; If the intention is to change it from 0-10-30-20-40-50 to 0-10-20-30-40-50, it would have been (in my design) exchanging node 3 and node 4 below. null,1,0 1,2,10 <-- node 2 2,3,30 <-- node 3 3,4,20 <-- node 4 4,5,40 5,null,50 Now, it can be done by: begin; update linkedlist set prevnode=2 where prevnode=3; -- node 4 = (2,4,20) update linkedlist set prevnode=3 where nextnode=3; -- node 3 = (3,3,30) update linkedlist set nextnode=3 where prevnode=2; -- node 4 = (2,3,20) update linkedlist set nextnode=4 where nextnode=3; -- node 3 = (3,4,30) commit; achieving the same. ... 2,3,20 <-- node 4, originally 3,4,30 <-- node 3, originally ... "node" will be more cost efficient if we insert an item at the beginning of a long list, for example insert (2,3,100) before node 3 (2,3,20), but at least the sql is simple; update linkedlist set prevnode = prevnode + 1 where prevnode > 1; update linkedlist set nextnode = nextnode + 1 where nextnode > 2; and then do insert (2,3,xxx) This method can also be used for reordering. The usefulness of the "node" will depend on the economics of these update operations over keeping one more field. But I think this is more of an exercise, and functions would be the proper way for complex operations. Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
