pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreturning a recordset from PLpg/SQL
11+ messages / 6 participants
[nested] [flat]
* returning a recordset from PLpg/SQL
@ 2004-02-29 12:36 Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-06-09 08:58 ` Re: returning a recordset from PLpg/SQL Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 11+ messages in thread
From: Terence Kearns @ 2004-02-29 12:36 UTC (permalink / raw)
To: pgsql-sql
Looking at the datatypes you can RETURN, it doesn't seem that there is a
way to return a recordset
I tried
RETURNS SETOF RECORD
but that doesn't work
I even tried
RETURNS SETOF fooTable%ROWTYPE
What I would like to do is not that simple, I need to be able to
build/declare a record definition with an arbitary list of columns and
then return a set of that record which has it's values populated by
queries and calculations inside the procedure.
returning an array is not a solution because I need to name all the
columns and they will have different data types.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
@ 2004-02-29 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-02-29 15:11 UTC (permalink / raw)
To: terence.kearns@canberra.edu.au; +Cc: pgsql-sql
Terence Kearns <terencek@isd.canberra.edu.au> writes:
> I tried
> RETURNS SETOF RECORD
> but that doesn't work
Sure it does, if you use it correctly. Better show us what you did.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-01 15:19 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Terence Kearns @ 2004-03-01 15:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Tom Lane wrote:
> Terence Kearns <terencek@isd.canberra.edu.au> writes:
>
>>I tried
>>RETURNS SETOF RECORD
>>but that doesn't work
>
>
> Sure it does, if you use it correctly. Better show us what you did.
>
> regards, tom lane
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
I'll write some code which I would like to work.
Here's the relevent part of the schema:
(see attached gif for full diagram)
CREATE DOMAIN docs.context
AS varchar(32)
NOT NULL;
CREATE TABLE docs.documents
(
doc_id int4 NOT NULL DEFAULT nextval('docs.seq_docs_doc_id'::text),
doc_title varchar(256) NOT NULL,
doc_summary varchar(512),
doc_folder_id int4 NOT NULL,
doc_sort_index int4,
CONSTRAINT pkey_docs PRIMARY KEY (doc_id),
CONSTRAINT fkey_documents_folder_id FOREIGN KEY (doc_folder_id)
REFERENCES docs.folders (folder_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_doc_id_folder_id UNIQUE (doc_id, doc_folder_id)
) WITHOUT OIDS;
COMMENT ON TABLE docs.documents IS 'This table records a LOGICAL
document (as opposed to it\'s physical incarnation). Metadata plus extra
attribute_values are recorded against this entity. Details regarding
physical manifestations of these documents are contained in the
document_instance table which might contain versions of the same
document, but in different formats or in different locations (mirrors).
Versioning information is also kept as a field in the document instance
table (and not in a dedicated document version table for reasons of
performance and simplicity).';
CREATE TABLE docs.attribute_profiles
(
att_profile_id int4 NOT NULL DEFAULT
nextval('docs.seq_attribute_profiles_att_profile'::text),
att_profile_title varchar(128) NOT NULL,
att_profile_description text,
att_profile_context docs.context,
CONSTRAINT pkey_attribute_profiles PRIMARY KEY (att_profile_id),
CONSTRAINT fkey_att_profile_context FOREIGN KEY (att_profile_context)
REFERENCES docs.contexts (context) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_att_profile_title_context UNIQUE
(att_profile_title, att_profile_context)
) WITHOUT OIDS;
COMMENT ON TABLE docs.attribute_profiles IS 'This table keeps a list of
entity attribute collections. This list is divided into application
contexts. The application can then select from a list of profiles in
it\'s own context and use each profile to build the data entry form.
Form building is the main usage of this table. Insert and lookup of user
data does not require this table. The attribute_value table(s) is used
for that.';
CREATE TABLE docs.entity_attributes
(
att_id int4 NOT NULL DEFAULT
nextval('docs.seq_entity_attribute_id'::text),
att_profile_id int4 NOT NULL,
att_name varchar(128) NOT NULL,
att_sort_index int4,
att_data_type varchar(32) NOT NULL,
att_is_metadata bool NOT NULL DEFAULT false,
CONSTRAINT pkey_entity_attributes PRIMARY KEY (att_id),
CONSTRAINT fkey_entity_attribute_profile_id FOREIGN KEY
(att_profile_id) REFERENCES docs.attribute_profiles (att_profile_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT unique_att_name_profile_id UNIQUE (att_name, att_profile_id),
CONSTRAINT check_entity_att_data_type CHECK att_data_type::text =
'timestamp'::text OR att_data_type::text = 'varchar'::text OR
att_data_type::text = 'text'::text OR att_data_type::text = 'integer'::text
) WITHOUT OIDS;
COMMENT ON TABLE docs.entity_attributes IS 'This table contains a list
of user-defined (developer defined) entity fields/attributes. It is used
to facilitate document entity exstensibility at runtime. In other words,
you can add fields to the document information form without ALTERing the
physical data structure of this schema. Corresponding user-data is not
stored here, but in the attribute_value table(s). IMPORTANT: see
comments on the att_data_type field.';
CREATE TABLE docs.document_attribute_values
(
entity_att_id int4 NOT NULL,
doc_id int4 NOT NULL,
value_timestamp timestamp,
value_varchar varchar(256),
value_text text,
value_integer int4,
CONSTRAINT pkey_document_attribute_values PRIMARY KEY (entity_att_id,
doc_id),
CONSTRAINT fkey_document_attribute_values_att_id FOREIGN KEY
(entity_att_id) REFERENCES docs.entity_attributes (att_id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
CONSTRAINT fkey_document_attribute_values_doc_id FOREIGN KEY (doc_id)
REFERENCES docs.documents (doc_id) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITHOUT OIDS;
COMMENT ON TABLE docs.document_attribute_values IS 'This table contains
user entries for document entity_attributes. The att_data_type field
contained in the entity_attributes table identified by value_att_id
points to the actual column in this table which ultimately stores the
target data. In other words, apart from the entity_att_id and the doc_id
field, only one out of the other fields is ever populated.';
So here's the desired function:
(of course, there's probably a whole bunch of reasons why this sketched
code won't work, I just want to know if the same outcome is at all
possible).
CREATE FUNCTION "docs"."details_for_profile" (docs.context, VARCHAR)
RETURNS SETOF "RECORD" AS'
DECLARE
profile_context ALIAS FOR $1;
profile_title ALIAS FOR $2;
result_row record;
lookup_row record;
str_temp text;
int_temp int4;
BEGIN
str_temp := '''';
FOR lookup_row IN SELECT att.*
FROM attribute_profiles prf, entity_attributes att
WHERE prf.att_profile_context = profile_context
AND prf.att_profile_title = profile_title
AND prf.att_profile_id = att.att_profile_id
LOOP
str_temp := str_temp
|| lookup_row.att_name
|| lookup_row.att_data_type'','' ;
END LOOP;
EXECUTE ''CREATE TYPE tmp_type (''
|| ''doc_id int4,''
|| ''doc_title varchar(256),''
|| ''doc_summary varchar(512),''
|| ''folder_id int4,''
|| ''sort_index int4,''
|| str_temp
|| ''profile_id int4''
'')'';
result_row tmp_type;
int_temp := 0;
FOR lookup_row IN SELECT d.*, v.*, att.att_name, att.att_data_type
FROM documents d, document_attribute_values v, entity_attributes att
WHERE d.doc_id = v.doc_id AND v.entity_att_id = att.att_id
ORDER BY d.doc_id
LOOP
IF int_temp != lookup_row.doc_id THEN
IF int_temp !=0 THEN
RETURN NEXT result_row;
END IF;
int_temp := lookup_row.doc_id;
result_row.doc_id := lookup_row.doc_id;
result_row.doc_title := lookup_row.doc_title;
result_row.doc_summary := lookup_row.doc_summary;
result_row.folder_id := lookup_row.folder_id;
result_row.doc_id := lookup_row.doc_id;
result_row.profile_id = lookup_row.profile_id;
END IF;
EXECUTE ''result_row.'' || lookup_row.att_name
|| '' := v.value_'' || lookup_row.att_data_type;
END;
RETURN NEXT result_row;
RETURN;
END;
'LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Attachments:
docs schema.gif (image/gif, 20.3K) download
view image
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
@ 2004-03-01 15:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 23:57 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
0 siblings, 2 replies; 11+ messages in thread
From: Stephan Szabo @ 2004-03-01 15:31 UTC (permalink / raw)
To: terence.kearns@canberra.edu.au; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Tue, 2 Mar 2004, Terence Kearns wrote:
> Tom Lane wrote:
> > Terence Kearns <terencek@isd.canberra.edu.au> writes:
> >
> >>I tried
> >>RETURNS SETOF RECORD
> >>but that doesn't work
> >
> >
> > Sure it does, if you use it correctly. Better show us what you did.
> >
> > regards, tom lane
>
> Well I haven't yet done anything because I couldn't get anything to
> compile which returned SETOF RECORD..
As a starting point, SETOF "RECORD" is different from SETOF RECORD given
PostgreSQL's fold case to lower case for unquoted names.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-03-01 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 18:45 ` Re: returning a recordset from PLpg/SQL Joe Conway <mail@joeconway.com>
2004-03-02 00:53 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2004-03-01 17:10 UTC (permalink / raw)
To: terence.kearns@canberra.edu.au; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 2 Mar 2004, Terence Kearns wrote:
>> Well I haven't yet done anything because I couldn't get anything to
>> compile which returned SETOF RECORD..
> As a starting point, SETOF "RECORD" is different from SETOF RECORD given
> PostgreSQL's fold case to lower case for unquoted names.
Also, you can hardly expect a function to return a rowtype that doesn't
even exist until the function executes --- how the heck is the parser
supposed to make sense of the calling query? So the "execute create
type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
will let you return a rowtype that is not known fully at the time the
function is written, but the rowtype does have to be known when the
calling query is parsed.
You might be able to replace the CREATE TYPE with an anonymous record
type in the calling query:
select ...
from details_for_profile(...) as x(doc_id int4,
doc_title varchar(256),
...);
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-01 18:45 ` Joe Conway <mail@joeconway.com>
2004-03-02 03:59 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 1 reply; 11+ messages in thread
From: Joe Conway @ 2004-03-01 18:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: terence.kearns@canberra.edu.au, Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>>On Tue, 2 Mar 2004, Terence Kearns wrote:
>>>Well I haven't yet done anything because I couldn't get anything to
>>>compile which returned SETOF RECORD..
>
>>As a starting point, SETOF "RECORD" is different from SETOF RECORD given
>>PostgreSQL's fold case to lower case for unquoted names.
>
> Also, you can hardly expect a function to return a rowtype that doesn't
> even exist until the function executes --- how the heck is the parser
> supposed to make sense of the calling query? So the "execute create
> type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
> will let you return a rowtype that is not known fully at the time the
> function is written, but the rowtype does have to be known when the
> calling query is parsed.
>
> You might be able to replace the CREATE TYPE with an anonymous record
> type in the calling query:
>
> select ...
> from details_for_profile(...) as x(doc_id int4,
> doc_title varchar(256),
> ...);
A small improvement is to do a two-step process. From your app, you
first SELECT a function call that returns an SQL statement as a text
string, specific to att_data_type. Then you execute that as a second
step. For example:
--8<--------------------------
create table hdr(hid int primary key, context text);
insert into hdr values(1, 'test1');
insert into hdr values(2, 'test2');
create table att(aid int primary key, attname text, atttype text);
insert into att values (1, 'test_date', 'timestamp');
insert into att values (2, 'height', 'float8');
insert into att values (3, 'width', 'float8');
insert into att values (4, 'color', 'text');
create table det(
did int primary key,
hid int references hdr,
aid int references att,
val text
);
insert into det values(1,1,1,'15-mar-2004');
insert into det values(2,1,2,'3.14159');
insert into det values(3,1,3,'2.8');
insert into det values(4,1,4,'blue');
insert into det values(5,2,1,'16-mar-2004');
insert into det values(6,2,2,'2.34');
insert into det values(7,2,3,'3.28');
insert into det values(8,2,4,'red');
create or replace function exec_sql(int) returns setof record as '
DECLARE
lookup_row record;
v_atttype text := '''';
rec record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_atttype := lookup_row.atttype;
END LOOP;
FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
'' FROM hdr h, att a, det d '' ||
'' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' language plpgsql;
create or replace function write_sql(int) returns text as '
DECLARE
v_attname text := '''';
v_atttype text := '''';
v_result text;
lookup_row record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_attname := lookup_row.attname;
v_atttype := lookup_row.atttype;
END LOOP;
v_result := ''select hid, context, '' || v_attname ||
'' from exec_sql('' || $1 || '') as t(hid int, context
text, '' ||
v_attname || '' '' || v_atttype || '')'';
return v_result;
END;
' language plpgsql;
regression=# select write_sql(1);
write_sql
--------------------------------------------------------------------------------------------------
select hid, context, test_date from exec_sql(1) as t(hid int, context
text, test_date timestamp)
(1 row)
regression=# select hid, context, test_date from exec_sql(1) as t(hid
int, context text, test_date timestamp);
hid | context | test_date
-----+---------+---------------------
1 | test1 | 2004-03-15 00:00:00
2 | test2 | 2004-03-16 00:00:00
(2 rows)
regression=# select write_sql(2);
write_sql
-----------------------------------------------------------------------------------------
select hid, context, height from exec_sql(2) as t(hid int, context
text, height float8)
(1 row)
regression=# select hid, context, height from exec_sql(2) as t(hid int,
context text, height float8);
hid | context | height
-----+---------+---------
1 | test1 | 3.14159
2 | test2 | 2.34
(2 rows)
regression=# select write_sql(3);
write_sql
---------------------------------------------------------------------------------------
select hid, context, width from exec_sql(3) as t(hid int, context
text, width float8)
(1 row)
regression=# select hid, context, width from exec_sql(3) as t(hid int,
context text, width float8);
hid | context | width
-----+---------+-------
1 | test1 | 2.8
2 | test2 | 3.28
(2 rows)
regression=# select write_sql(4);
write_sql
-------------------------------------------------------------------------------------
select hid, context, color from exec_sql(4) as t(hid int, context
text, color text)
(1 row)
regression=# select hid, context, color from exec_sql(4) as t(hid int,
context text, color text);
hid | context | color
-----+---------+-------
1 | test1 | blue
2 | test2 | red
(2 rows)
--8<--------------------------
Hopefully this is close enough to what you are trying to do that it will
give you some ideas.
HTH,
Joe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 18:45 ` Re: returning a recordset from PLpg/SQL Joe Conway <mail@joeconway.com>
@ 2004-03-02 03:59 ` Terence Kearns <terencek@isd.canberra.edu.au>
0 siblings, 0 replies; 11+ messages in thread
From: Terence Kearns @ 2004-03-02 03:59 UTC (permalink / raw)
To: pgsql-sql
I've read through all the example code you've provided thoroughly and
there are definately some useful ideas there. I changed the design of
the document_attribute_values table to only have one field, a text
field, to store the value. As your examples demosntrates, I can simply
cast the text value to whatever the entity_attribute record [for that
value] specifies.
I also think a 2-step approach is the way to go.
Thanks.
Joe Conway wrote:
> Tom Lane wrote:
>
>> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>>
>>> On Tue, 2 Mar 2004, Terence Kearns wrote:
>>>
>>>> Well I haven't yet done anything because I couldn't get anything to
>>>> compile which returned SETOF RECORD..
>>
>>
>>> As a starting point, SETOF "RECORD" is different from SETOF RECORD given
>>> PostgreSQL's fold case to lower case for unquoted names.
>>
>>
>> Also, you can hardly expect a function to return a rowtype that doesn't
>> even exist until the function executes --- how the heck is the parser
>> supposed to make sense of the calling query? So the "execute create
>> type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
>> will let you return a rowtype that is not known fully at the time the
>> function is written, but the rowtype does have to be known when the
>> calling query is parsed.
>>
>> You might be able to replace the CREATE TYPE with an anonymous record
>> type in the calling query:
>>
>> select ...
>> from details_for_profile(...) as x(doc_id int4,
>> doc_title varchar(256),
>> ...);
>
>
> A small improvement is to do a two-step process. From your app, you
> first SELECT a function call that returns an SQL statement as a text
> string, specific to att_data_type. Then you execute that as a second
> step. For example:
> --8<--------------------------
>
> create table hdr(hid int primary key, context text);
> insert into hdr values(1, 'test1');
> insert into hdr values(2, 'test2');
>
> create table att(aid int primary key, attname text, atttype text);
> insert into att values (1, 'test_date', 'timestamp');
> insert into att values (2, 'height', 'float8');
> insert into att values (3, 'width', 'float8');
> insert into att values (4, 'color', 'text');
>
> create table det(
> did int primary key,
> hid int references hdr,
> aid int references att,
> val text
> );
> insert into det values(1,1,1,'15-mar-2004');
> insert into det values(2,1,2,'3.14159');
> insert into det values(3,1,3,'2.8');
> insert into det values(4,1,4,'blue');
> insert into det values(5,2,1,'16-mar-2004');
> insert into det values(6,2,2,'2.34');
> insert into det values(7,2,3,'3.28');
> insert into det values(8,2,4,'red');
>
> create or replace function exec_sql(int) returns setof record as '
> DECLARE
> lookup_row record;
> v_atttype text := '''';
> rec record;
> BEGIN
> FOR lookup_row IN SELECT * FROM att WHERE aid = $1
> LOOP
> v_atttype := lookup_row.atttype;
> END LOOP;
>
> FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
> '' FROM hdr h, att a, det d '' ||
> '' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
> LOOP
> RETURN NEXT rec;
> END LOOP;
> RETURN;
> END;
> ' language plpgsql;
>
> create or replace function write_sql(int) returns text as '
> DECLARE
> v_attname text := '''';
> v_atttype text := '''';
> v_result text;
> lookup_row record;
> BEGIN
> FOR lookup_row IN SELECT * FROM att WHERE aid = $1
> LOOP
> v_attname := lookup_row.attname;
> v_atttype := lookup_row.atttype;
> END LOOP;
> v_result := ''select hid, context, '' || v_attname ||
> '' from exec_sql('' || $1 || '') as t(hid int, context
> text, '' ||
> v_attname || '' '' || v_atttype || '')'';
> return v_result;
> END;
> ' language plpgsql;
>
> regression=# select write_sql(1);
> write_sql
> --------------------------------------------------------------------------------------------------
>
> select hid, context, test_date from exec_sql(1) as t(hid int, context
> text, test_date timestamp)
> (1 row)
>
> regression=# select hid, context, test_date from exec_sql(1) as t(hid
> int, context text, test_date timestamp);
> hid | context | test_date
> -----+---------+---------------------
> 1 | test1 | 2004-03-15 00:00:00
> 2 | test2 | 2004-03-16 00:00:00
> (2 rows)
>
> regression=# select write_sql(2);
> write_sql
> -----------------------------------------------------------------------------------------
>
> select hid, context, height from exec_sql(2) as t(hid int, context
> text, height float8)
> (1 row)
>
> regression=# select hid, context, height from exec_sql(2) as t(hid int,
> context text, height float8);
> hid | context | height
> -----+---------+---------
> 1 | test1 | 3.14159
> 2 | test2 | 2.34
> (2 rows)
>
> regression=# select write_sql(3);
> write_sql
> ---------------------------------------------------------------------------------------
>
> select hid, context, width from exec_sql(3) as t(hid int, context text,
> width float8)
> (1 row)
>
> regression=# select hid, context, width from exec_sql(3) as t(hid int,
> context text, width float8);
> hid | context | width
> -----+---------+-------
> 1 | test1 | 2.8
> 2 | test2 | 3.28
> (2 rows)
>
> regression=# select write_sql(4);
> write_sql
> -------------------------------------------------------------------------------------
>
> select hid, context, color from exec_sql(4) as t(hid int, context text,
> color text)
> (1 row)
>
> regression=# select hid, context, color from exec_sql(4) as t(hid int,
> context text, color text);
> hid | context | color
> -----+---------+-------
> 1 | test1 | blue
> 2 | test2 | red
> (2 rows)
>
> --8<--------------------------
>
> Hopefully this is close enough to what you are trying to do that it will
> give you some ideas.
>
> HTH,
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-02 00:53 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-04 21:01 ` Re: returning a recordset from PLpg/SQL Radu-Adrian Popescu <radu.popescu@aldratech.com>
1 sibling, 1 reply; 11+ messages in thread
From: Terence Kearns @ 2004-03-02 00:53 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
>> On Tue, 2 Mar 2004, Terence Kearns wrote:
>>
>>> Well I haven't yet done anything because I couldn't get anything to
>>> compile which returned SETOF RECORD..
>
>
>
>> As a starting point, SETOF "RECORD" is different from SETOF RECORD given
>> PostgreSQL's fold case to lower case for unquoted names.
>
>
>
> Also, you can hardly expect a function to return a rowtype that doesn't
> even exist until the function executes --- how the heck is the parser
> supposed to make sense of the calling query? So the "execute create
> type" part of this is nonsense, I'm afraid.
Right you are. I did mention that I didn't expect that code to work at
all, I just used it as an indicator or a desired outcome. I'm sorry if I
didn't make that clear enough. I just hoped that it would illustrate
what I'm trying to achieve. And that is:
"return a set of rows where the columns in that row are not yet determined."
> The SETOF RECORD mechanism
> will let you return a rowtype that is not known fully at the time the
> function is written, but the rowtype does have to be known when the
> calling query is parsed.
Interesting.
>
> You might be able to replace the CREATE TYPE with an anonymous record
> type in the calling query:
>
> select ...
> from details_for_profile(...) as x(doc_id int4,
> doc_title varchar(256),
> ...);
>
> regards, tom lane
>
yeah but then you're back to square one with dynamically building the
columns to match the anonymous type you declared in the query.
I've got some ideas about creating persistent types using RULEs on the
attribute_profiles table. So when someone INSERTs or UPDATEs an
attribute profile, a datatype going by the name 'profile_type_' ||
att_profile_id::text is created. That way the types are already known
and maybe can somehow be passed to the details_for_profile() prcedure.
I'll have to experiment and get back.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-02 00:53 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
@ 2004-03-04 21:01 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
0 siblings, 0 replies; 11+ messages in thread
From: Radu-Adrian Popescu @ 2004-03-04 21:01 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; +Cc: pgsql-sql
Terence Kearns wrote:
> Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >
> >> On Tue, 2 Mar 2004, Terence Kearns wrote:
> >>
> >>> Well I haven't yet done anything because I couldn't get anything to
> >>> compile which returned SETOF RECORD..
> >
> >
> >
> >> As a starting point, SETOF "RECORD" is different from SETOF RECORD
> given
> >> PostgreSQL's fold case to lower case for unquoted names.
> >
> >
> >
> > Also, you can hardly expect a function to return a rowtype that doesn't
> > even exist until the function executes --- how the heck is the parser
> > supposed to make sense of the calling query? So the "execute create
> > type" part of this is nonsense, I'm afraid.
>
>
> Right you are. I did mention that I didn't expect that code to work at
> all, I just used it as an indicator or a desired outcome. I'm sorry if
> I didn't make that clear enough. I just hoped that it would illustrate
> what I'm trying to achieve. And that is:
> "return a set of rows where the columns in that row are not yet
> determined."
>
> > The SETOF RECORD mechanism
>
> > will let you return a rowtype that is not known fully at the time the
> > function is written, but the rowtype does have to be known when the
> > calling query is parsed.
>
>
> Interesting.
>
> >
> > You might be able to replace the CREATE TYPE with an anonymous record
> > type in the calling query:
> >
> > select ...
> > from details_for_profile(...) as x(doc_id int4,
> > doc_title varchar(256),
> > ...);
> >
> > regards, tom lane
> >
>
> yeah but then you're back to square one with dynamically building the
> columns to match the anonymous type you declared in the query.
>
> I've got some ideas about creating persistent types using RULEs on the
> attribute_profiles table. So when someone INSERTs or UPDATEs an
> attribute profile, a datatype going by the name 'profile_type_' ||
> att_profile_id::text is created. That way the types are already known
> and maybe can somehow be passed to the details_for_profile() prcedure.
>
> I'll have to experiment and get back.
>
I'm sorry if this is just plain stupid - as I've not bothered to read
the full schema in your message - but wouldn't a cursor do ?
Since I'm mostly coding in Java, I find that this approach, while common
to both PGSQL and Oracle, works quite good. I've not
been able to use the cursor in psql although... By the way can someone
tell me how the heck am I supposed to get the results
from a cursor return from a pl/pgsql function ?
Example:
create or replace function SomeObject_read(int) returns refcursor as
'
declare
the_row refcursor;
v_id alias for $1;
begin
open the_row for
select *
from SomeObject
where id = v_id;
return the_row;
end;
' language plpgsql;
This works great from Java where I use a callable statement, do a "{ ? =
call SomeObject_read(?) }" query,
register the out parameter as OTHER, bind the parameter and get back a
ResultSet.
All fine and marry, but I can't figure out how to use _this_ form of the
function from psql. I've read and tried
the manual examples and they do work. However, I can't make this one work.
Thanks in advance,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
+40213212243
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Re: returning a recordset from PLpg/SQL Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Re: returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Re: returning a recordset from PLpg/SQL Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-03-01 23:57 ` Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 0 replies; 11+ messages in thread
From: Terence Kearns @ 2004-03-01 23:57 UTC (permalink / raw)
To: pgsql-sql
Stephan Szabo wrote:
> As a starting point, SETOF "RECORD" is different from SETOF RECORD given
> PostgreSQL's fold case to lower case for unquoted names.
>
Ahh! That will help :)
That's what you get when you use a silly IDE instead of a regular editor
like vi or notepad or something. Because I haven't written a procedure
for years (and even then, I only wrote one or two simple ones) I'd
forgotten the syntax so I thought using a dedicated prostgres procedure
editor would make it easier. I didn't even think to considder the double
quotes!
Thanks for pointing that out.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: returning a recordset from PLpg/SQL
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
@ 2004-06-09 08:58 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2004-06-09 08:58 UTC (permalink / raw)
To: terence.kearns@canberra.edu.au; +Cc: pgsql-sql
Terence Kearns wrote:
> Looking at the datatypes you can RETURN, it doesn't seem that there is a
> way to return a recordset
>
> I tried
> RETURNS SETOF RECORD
> but that doesn't work
>
> I even tried
> RETURNS SETOF fooTable%ROWTYPE
>
> What I would like to do is not that simple, I need to be able to
> build/declare a record definition with an arbitary list of columns and
> then return a set of that record which has it's values populated by
> queries and calculations inside the procedure.
Declare your datatype (CREATE TYPE ...) and return a SETOF that. What
you can't do is declare your datatype inside your function. Clearly
PostgreSQL needs to know what type is being returned while parsing the
function, so you can't have a "dynamic type" (if such an idea has any
meaning at all).
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-06-09 08:58 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-29 12:36 returning a recordset from PLpg/SQL Terence Kearns <terencek@isd.canberra.edu.au>
2004-02-29 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 15:19 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-01 15:31 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-01 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-01 18:45 ` Joe Conway <mail@joeconway.com>
2004-03-02 03:59 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-02 00:53 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-03-04 21:01 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2004-03-01 23:57 ` Terence Kearns <terencek@isd.canberra.edu.au>
2004-06-09 08:58 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox