Thread: dynamic OUT parameters?

dynamic OUT parameters?

From
Gerardo Herzig
Date:
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


Re: dynamic OUT parameters?

From
Craig Ringer
Date:
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


Re: dynamic OUT parameters?

From
Tom Lane
Date:
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


Re: dynamic OUT parameters?

From
gherzig@fmed.uba.ar
Date:
> 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



Re: dynamic OUT parameters?

From
Craig Ringer
Date:
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


Re: dynamic OUT parameters?

From
gherzig@fmed.uba.ar
Date:
> 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




Re: dynamic OUT parameters?

From
Craig Ringer
Date:
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


Re: dynamic OUT parameters?

From
Ivan Sergio Borgonovo
Date:
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



Re: dynamic OUT parameters?

From
Pavel Stehule
Date:
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
>