returning parameters from function

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/

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)

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

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

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)