[SQL] plpgsql loop question
hi, i have a table called "zones": idzone, zone_name and several tables called zonename_records (same structure), where zonename is one of the zone_name in the "zones" table. What i want to do is a function that union all of this tables dinamically based on "zones" table, this is what i've done so far: CREATE OR REPLACE FUNCTION get_all_records() RETURNS SETOF record AS $BODY$DECLARE zones record; recs record; BEGIN for zones in select lower(zone_name) as n from zones loop for recs in select * from quote_ident(zones.n || '_records') loop return next recs; end loop; end loop; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; but i get this error! ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "get_all_records" line 9 at RETURN NEXT Andrea -- 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] plpgsql loop question
On 2/10/2010 11:29 AM, Andrea Visinoni wrote: > hi, > i have a table called "zones": idzone, zone_name > and several tables called zonename_records (same structure), where > zonename is one of the zone_name in the "zones" table. > What i want to do is a function that union all of this tables > dinamically based on "zones" table, this is what i've done so far: > > CREATE OR REPLACE FUNCTION get_all_records() > RETURNS SETOF record AS > $BODY$DECLARE > zones record; > recs record; > BEGIN > for zones in select lower(zone_name) as n from zones loop > for recs in select * from quote_ident(zones.n || '_records') loop > return next recs; > end loop; > end loop; > RETURN; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > > but i get this error! > > ERROR: wrong record type supplied in RETURN NEXT > CONTEXT: PL/pgSQL function "get_all_records" line 9 at RETURN NEXT > > Andrea > Pg will not auto build the columns outputted from generic type record. The function needs to describe what the output is going to look like. The function can inherit the layout from a table or create the new pg data type but PG does not know what the data looks like to create the result set. . All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- 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] 'image' table with relationships to different objects
On Tue, Feb 09, 2010 at 08:01:35AM -0700, Rob Sargent wrote: > You can also invert this, making all the image owner share a common base > table and then images are dependent on that base > > base (id, type) where type is an enumeration or some such > person (id, name, etc) where id is FK to base id > locations (id, address, etc) where id is FK to base.id > events(id, date, etc) where id is FK to base.id > images(id, baseid) where baseid is FK to base.id > > views across base to the "data" tables for easier sql if desired > ORM: person location and event would inherit from base This is intriguing. How do I manage the auto-incrementing 'id' serial on children tables 'person', 'location' and 'event'? Thanks, -- http://www.critikart.net -- 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] 'image' table with relationships to different objects
On 2/9/2010 6:59 AM, Richard Huxton wrote: > On 09/02/10 07:49, Louis-David Mitterrand wrote: >> Hello, >> >> In my database I have different object types (person, location, event, >> etc.) all of which can have several images attached. >> >> What is the best way to manage a single 'image' table with relationships >> to (potentially) many different object types while keeping referrential >> integrity (foreign keys)? > > The "clean" way to do this would be with a number of joining tables: > > images(img_id, file_name, title ...) > persons (psn_id, first_name, last_name, ...) > locations (loc_id, loc_name, lat, lon, ...) > events(evt_id, evt_name, starts_on, ends_on, ...) > > person_images (psn_id, img_id) > location_images (loc_id, img_id) > event_images(evt_id, img_id) Another why that reduces the number of tables and simplifies the selects and allows linking to unlimited number of objects is something like this is something like this Create table images (img_id serial , img_data bytea ); Create table image_related( img_rel_id serial, img_rel_img_id int, img_link_key int, img_link_from char(10) ); Create table persons (psn_id serial, first_name text, last_name text) ; create table locations (loc_id serial, loc_name text) ; create table events(evt_id serial, evt_name text, starts_on timestamp, ends_on timestamp); Insert into images values (default, null), (default, null), (default, null); Insert into persons values ( default, 'me me', 'yes itsme'); Insert into locations values (default, 'I home'); Insert into events values (default, 'friends party', now(), now() ); insert into image_related values (default, 1, 1, 'persons'), (default ,2, 1, 'events'), (default ,3, 1, 'locations'), (default , 2, 1, 'persons'); Select img_data, first_name from persons, images, image_related where img_id = img_rel_img_id and img_link_key = psn_id and img_link_from = 'persons' then create a rule on img_related before insert and update to make sure the parent records exist for integrity checks. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- 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] plpgsql loop question
On 02/10/2010 08:29 AM, Andrea Visinoni wrote: hi, i have a table called "zones": idzone, zone_name and several tables called zonename_records (same structure), where zonename is one of the zone_name in the "zones" table. What i want to do is a function that union all of this tables dinamically based on "zones" table, this is what i've done so far: CREATE OR REPLACE FUNCTION get_all_records() RETURNS SETOF record AS $BODY$DECLARE zones record; recs record; BEGIN for zones in select lower(zone_name) as n from zones loop for recs in select * from quote_ident(zones.n || '_records') loop return next recs; end loop; end loop; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; but i get this error! ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "get_all_records" line 9 at RETURN NEXT Andrea One thing I would do is rename your zones record variable. pgsql does not deal well with a variable having the same name as a schema object, in this case your table zones. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
