Thread: dynamic OUT parameters?
Hi all. I have a situation here: I have to make a 'report' for a table.
I'll try to explain the functionality needed:
Lets say i have a table like this
\d people
name varchar
id_city integer (references city(id_city))
\d city
id_city integer
city_name varchar
id_country (references country (id_country))
\d country
id_country integer
country_name varchar
So, if i call report('people') i should return
name |city |country
Philip |Portland |UEA
Manuel |Madrid |España
And so on. This is: "Do *not* include those fields who are FK, instead,
put the referenced keyword from the referenced table".
It is not hard to do this in a query. Is not neither hard to make it a
function. But i have many tables to 'report', and the application leader
does not want querys in the program, just procedures call. So i would
like to have a generic function who should have the logic of 'selecting
the columns to show'. And then i have to ask:
1) There is a way to make a function returning "any amount of any type
of arguments"?
2) Can i make a special type "on_the_fly" and returning setof "that_type"?
3) Should (and/or can) i make a view "on_the_fly" and return from
"that_view"?
Comments awaiting. I hope the explanation is clear enough.
Thanks!
Gerardo
Gerardo Herzig wrote: > 1) There is a way to make a function returning "any amount of any type > of arguments"? RETURNS SETOF RECORD The application must, however, know what columns will be output by the function ahead of time and call it using an explicit column declaration list. For example, the following function returns a table of width `_ncols' columns repeated over `_ncols' records: CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD AS $$ DECLARE _out RECORD; _stm text; _i integer; BEGIN _stm = 'SELECT 1'; FOR _i IN 2.._ncols LOOP _stm = _stm || ', ' || _i; END LOOP; _stm = _stm || ' FROM generate_series(1,'|| _ncols || ');' ; FOR _out IN EXECUTE _stm LOOP RETURN NEXT _out; END LOOP; END; $$ LANGUAGE 'plpgsql'; Because Pg must know what the return columns will be before the function is called, you can't just call it as `dyncol(4)' : test=> SELECT dyncol(4); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT you must instead specify a table alias with a column definition, eg: test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d INTEGER);a | b | c | d ---+---+---+---1 | 2 | 3 | 41 | 2 | 3 | 41 | 2 | 3 | 41 | 2 | 3 | 4 (4 rows) Of course, nothing stops you from writing another function that provides this information to the application, so it can call the first function to get the information required to correctly call your dynamic reporting function. > 2) Can i make a special type "on_the_fly" and returning setof "that_type"? You're better off using SETOF RECORD, at least in my opinion. -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
>> 2) Can i make a special type "on_the_fly" and returning setof "that_type"?
> You're better off using SETOF RECORD, at least in my opinion.
Another possibility is to return a cursor.
regards, tom lane
> Gerardo Herzig wrote: > >> 1) There is a way to make a function returning "any amount of any type >> of arguments"? > > RETURNS SETOF RECORD > > The application must, however, know what columns will be output by the > function ahead of time and call it using an explicit column declaration > list. For example, the following function returns a table of width > `_ncols' columns repeated over `_ncols' records: > > CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD > AS > $$ > DECLARE > _out RECORD; > _stm text; > _i integer; > BEGIN > _stm = 'SELECT 1'; > FOR _i IN 2.._ncols LOOP > _stm = _stm || ', ' || _i; > END LOOP; > _stm = _stm || ' FROM generate_series(1,' || _ncols || ');' ; > FOR _out IN EXECUTE _stm LOOP > RETURN NEXT _out; > END LOOP; > END; > $$ LANGUAGE 'plpgsql'; > > > Because Pg must know what the return columns will be before the function > is called, you can't just call it as `dyncol(4)' : > > test=> SELECT dyncol(4); > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT > > you must instead specify a table alias with a column definition, eg: > > test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d > INTEGER); > a | b | c | d > ---+---+---+--- > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > (4 rows) > > > Of course, nothing stops you from writing another function that provides > this information to the application, so it can call the first function > to get the information required to correctly call your dynamic reporting > function. > >> 2) Can i make a special type "on_the_fly" and returning setof >> "that_type"? > > You're better off using SETOF RECORD, at least in my opinion. > > -- > Craig Ringer Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD forces the use of OUT parameters. I will give your idea a try. Thanks Craig! Gerardo
gherzig@fmed.uba.ar wrote: > Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD > forces the use of OUT parameters. I will give your idea a try. Tom Lane's point about using a refcursor is (unsurprisingly) a good one. If you return a refcursor from your function, you don't have to do any special work to call the function, and you can (with most DB access APIs) FETCH records from the cursor rather conveniently. See: http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html -- Craig Ringer
> gherzig@fmed.uba.ar wrote: > >> Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD >> forces the use of OUT parameters. I will give your idea a try. > > Tom Lane's point about using a refcursor is (unsurprisingly) a good one. > If you return a refcursor from your function, you don't have to do any > special work to call the function, and you can (with most DB access > APIs) FETCH records from the cursor rather conveniently. > > See: > > http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html > > -- > Craig Ringer > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Well, aparenty my problem remains, because the boss want that the programmers just need to call select * from report('table_name'). Following your previous sample function, im forced to use it in the form select * from report('table_name') as x(a int, b varchar, c int), and that "as x(...)" is the kind of thing hes triyng to avoid. Same feeling about fetching records at application level. To bad for me, im affraid :( Gerardo
gherzig@fmed.uba.ar wrote:
> Well, aparenty my problem remains, because the boss want that the
> programmers just need to call
> select * from report('table_name').
Then, AFAIK, you're not going to have much luck, as Pg needs to know the
columns that'll be output before the function is called. Applications
and DB access interfaces also usually also need to know the column list
beforehand.
If you return a refcursor you can at least use:
SELECT report('table_name');
FETCH ALL IN 'cursorname';
The documentation has a more full example:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465
Personally, I find it difficult to imagine what could be wrong with that.
--
Craig Ringer
On Sun, 01 Feb 2009 12:42:12 +0900
Craig Ringer <craig@postnewspapers.com.au> wrote:
> gherzig@fmed.uba.ar wrote:
>
> > Well, aparenty my problem remains, because the boss want that the
> > programmers just need to call
> > select * from report('table_name').
>
> Then, AFAIK, you're not going to have much luck, as Pg needs to
> know the columns that'll be output before the function is called.
> Applications and DB access interfaces also usually also need to
> know the column list beforehand.
>
> If you return a refcursor you can at least use:
>
> SELECT report('table_name');
> FETCH ALL IN 'cursorname';
>
> The documentation has a more full example:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465
>
> Personally, I find it difficult to imagine what could be wrong
> with that.
I've followed this thread with interest.
I'm starting to manage larger and larger code base of plpsql.
What I find hard to achieve is code reuse and implication of some
techniques to performances.
I didn't see many examples that exploit cursors around.
Does anyone have some pointer to some more enlightening use of
cursors for code reuse and OOP?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Hello try to look on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html regards Pavel Stehule 2009/2/1 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Sun, 01 Feb 2009 12:42:12 +0900 > Craig Ringer <craig@postnewspapers.com.au> wrote: > >> gherzig@fmed.uba.ar wrote: >> >> > Well, aparenty my problem remains, because the boss want that the >> > programmers just need to call >> > select * from report('table_name'). >> >> Then, AFAIK, you're not going to have much luck, as Pg needs to >> know the columns that'll be output before the function is called. >> Applications and DB access interfaces also usually also need to >> know the column list beforehand. >> >> If you return a refcursor you can at least use: >> >> SELECT report('table_name'); >> FETCH ALL IN 'cursorname'; >> >> The documentation has a more full example: >> >> http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465 >> >> Personally, I find it difficult to imagine what could be wrong >> with that. > > I've followed this thread with interest. > I'm starting to manage larger and larger code base of plpsql. > What I find hard to achieve is code reuse and implication of some > techniques to performances. > > I didn't see many examples that exploit cursors around. > Does anyone have some pointer to some more enlightening use of > cursors for code reuse and OOP? > > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >