Thread: returning parameters from function

Hi!

Is there any plan to add implicit declaration of returning parameters
for functions?
Something like:
create function list(in a int) returns setof implicit record as
$$
if a=1 then select * from table1;
else select * from table2;
end if;
$$
languge sql;

which would than dynamically create output parameters depending on
selected query inside.

I think this would be very helpful, and is one of the
most missing features from MSSQL for me.

One other feature that I miss, but no so much is:
storing the entire function, not just the body
(because of this pg cant remember exactly how I wrote the
function, and if there are many parameters inside I get a looong first line)


Regards,
Rikard

On Tue, Dec 12, 2006 at 10:30:07AM +0100, Rikard Pavelic wrote:
> Hi!
>
> Is there any plan to add implicit declaration of returning parameters
> for functions?
> Something like:
> create function list(in a int) returns setof implicit record as

<snip>

Just "setof record" will do. As for the implicit declaration of
variable names, that's harder. I don't know if you can do that without
making things very ambiguous.

> One other feature that I miss, but no so much is:
> storing the entire function, not just the body
> (because of this pg cant remember exactly how I wrote the
> function, and if there are many parameters inside I get a looong first line)

You should get out what you put in, but it won't remember spacing
outside the function body because the lexer eats that.

I have a file with the function as I want it and edit that. That way I
can have the function exactly how I like it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Tue, Dec 12, 2006 at 10:30:07AM +0100, Rikard Pavelic wrote:
> Hi!
>
> Is there any plan to add implicit declaration of returning parameters
> for functions?
> Something like:
> create function list(in a int) returns setof implicit record as

You can use a SETOF function as:

CREATE OR REPLACE FUNCTION get_test_data (numeric)
    RETURNS SETOF RECORD AS
$$
DECLARE
    temp_rec    RECORD;
BEGIN
    FOR temp_rec IN (SELECT ename FROM emp WHERE sal > $1)
    LOOP
        RETURN NEXT temp_rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-----------------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Shoaib Mir wrote:
> You can use a SETOF function as:
>
> CREATE OR REPLACE FUNCTION get_test_data (numeric)
>     RETURNS SETOF RECORD AS
> $$
> DECLARE
>     temp_rec    RECORD;
> BEGIN
>     FOR temp_rec IN (SELECT ename FROM emp WHERE sal > $1)
>     LOOP
>         RETURN NEXT temp_rec;
>     END LOOP;
>     RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
This doesn't work. ;(
I get ERROR: a column definition list is required for functions
returning "record"
SQL state: 42601
when running this function

Regards,
Rikard

You have to call the function in the following form:

SELECT * FROM get_test_data(1) AS (field1 type, field2 type, ...)

In words, you have to tell the database how the data returned by the
function has to be interpreted.

Greetings,
Matthias

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Rikard Pavelic
> Sent: Tuesday, December 12, 2006 3:06 PM
> To: Shoaib Mir; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] returning parameters from function
>
> This doesn't work. ;(
> I get ERROR: a column definition list is required for functions
> returning "record"
> SQL state: 42601
> when running this function
>
> Regards,
> Rikard
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org
> so that your
>        message can get through to the mailing list cleanly
>

Martijn van Oosterhout wrote:
> <snip>
>
> Just "setof record" will do. As for the implicit declaration of
> variable names, that's harder. I don't know if you can do that without
> making things very ambiguous.
>
I know setof record will do if I explicitly name OUT parameters.
But I want Postgre to figure out for himself what parameters to return
as out parameters.

I don't see why it would make things very ambiguous.
Postgre now allows only one function with same set of input parameters.
So if you have function1(in int, out varchar) of course you can't have
another function1(in int)
What it could lead to is problems at running that were not seen at
designing function.

> You should get out what you put in, but it won't remember spacing
> outside the function body because the lexer eats that.
>
> I have a file with the function as I want it and edit that. That way I
> can have the function exactly how I like it.
>
> Have a nice day,
>

Yeah, I understand pg needs only the body to execute the function, and
having another field in
pg_catalog.pg_proc besides prosrc maybe seems like wasting space but it
would make my life
as a developer a lot easier.

Regards,
Rikard

Rikard Pavelic wrote:
> Martijn van Oosterhout wrote:
>> <snip>
>>
>> Just "setof record" will do. As for the implicit declaration of
>> variable names, that's harder. I don't know if you can do that without
>> making things very ambiguous.
>>
> I know setof record will do if I explicitly name OUT parameters.
> But I want Postgre to figure out for himself what parameters to return
> as out parameters.
>
> I don't see why it would make things very ambiguous.

Think about what happens if you use such a function in a join. How does
the planner know what to do? What about a prepared query?

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> Rikard Pavelic wrote:
>> I know setof record will do if I explicitly name OUT parameters.
>> But I want Postgre to figure out for himself what parameters to
>> return as out parameters.
>>
>> I don't see why it would make things very ambiguous.
>
> Think about what happens if you use such a function in a join. How
> does the planner know what to do? What about a prepared query?

Good point.

Well, so much about that ;(

Regards,
Rikard

You can use it as:

SELECT * FROM get_test_data(1000) AS t1 (emp_name VARCHAR);

--------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)