Returning RECORD from PGSQL without custom type?

Hello

2008/5/10 D. Dante Lorenso <dante@larkspark.com>:

Instead of doing this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

Standard syntax via ANSI SQL is
CREATE FUNCTION foo(params)
RETURNS TABLE(fields of output table) AS
$$ ...

$$

Because this is the only function that will be returning that TYPE and I
don't want to have to create a separate type definition just for the return
results of this function.

Maybe even more cool would be if the OUT record was already defined so that
I could simply select into that record to send our new rows:

RETURN NEXT OUT;

OUT.col1name := 12345;
RETURN NEXT OUT;

SELECT 12345, 'sample'
INTO OUT.col1name, OUT.col2name;
RETURN NEXT OUT;

it's good idea - it was probably main problem of last patch in
plpgsql. In this syntax is clear what is output, so RETURN NEXT
statement can be without params. I am only not sure about name of
default variable - maybe result is better.

Regards
Pavel Stehule

Pavel Stehule wrote:

Hello
2008/5/10 D. Dante Lorenso <dante@larkspark.com>:

Instead of doing this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

Standard syntax via ANSI SQL is
CREATE FUNCTION foo(params)
RETURNS TABLE(fields of output table) AS
$$ ...
$$

Ah, this sound almost exactly like what I'm wanting! So ... you are
saying that developers are working on something like? I'm running 8.3
... would I find this feature in 8.4 or is it still not included in any
release?

Because this is the only function that will be returning that TYPE and I
don't want to have to create a separate type definition just for the return
results of this function.

Maybe even more cool would be if the OUT record was already defined so that
I could simply select into that record to send our new rows:
RETURN NEXT OUT;
OUT.col1name := 12345;
RETURN NEXT OUT;
SELECT 12345, 'sample'
INTO OUT.col1name, OUT.col2name;
RETURN NEXT OUT;

it's good idea - it was probably main problem of last patch in
plpgsql. In this syntax is clear what is output, so RETURN NEXT
statement can be without params. I am only not sure about name of
default variable - maybe result is better.

Yeah, RESULT works too. I'm not particular about what it has to be ...
just that something like that might exist.

Where can I go to follow development of this or test it out? I see some
old threads now that I know what to look for:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
http://archives.postgresql.org/pgsql-patches/2007-02/msg00216.php
http://archives.postgresql.org/pgsql-patches/2007-02/msg00341.php
http://archives.postgresql.org/pgsql-hackers/2007-09/msg01079.php

I want to make sure this patch/proposal covers my needs and
expectations. Specifically I want to return records that are not simple
a straight query:

CREATE OR REPLACE FUNCTION foo(f integer)
RETURNS TABLE(a int, b int) AS
$$
DECLARE
my_a INT;
my_b INT;
BEGIN
-- 1) perhaps like this
SELECT 1, 2
INTO RESULT.a, RESULT.b;
RETURN NEXT RESULT;

-- 2) maybe like this
RETURN NEXT 3, 4; -- a=3, b=4

-- 3) how about like this
my_a := 5;
my_b := 6;
RETURN NEXT my_a, my_b;

-- 4) maybe like this
RETURN NEXT QUERY SELECT a, b FROM sometable x WHERE x.f = f;

-- done
RETURN;
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT a, b
FROM foo(20);

Results:

a | b
---+----
1 | 2 <-- 1)
3 | 4 <-- 2)
5 | 6 <-- 3)
... <-- 4) results from sometable WHERE x.f = 20

What do you think, will I be able to do all of this?

-- Dante

"D. Dante Lorenso" <dante@lorenso.com> writes:

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS

You realize of course that you can do this *today* if you use OUT
parameters?

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint,
OUT col1name BIGINT, OUT col2name TEXT, ...)
RETURNS SETOF RECORD AS

The TABLE syntax is a bit more standards-compliant maybe, but it's not
offering any actual new functionality.

regards, tom lane

2008/5/12 Tom Lane <tgl@sss.pgh.pa.us>:

"D. Dante Lorenso" <dante@lorenso.com> writes:

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS

You realize of course that you can do this *today* if you use OUT
parameters?

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint,
OUT col1name BIGINT, OUT col2name TEXT, ...)
RETURNS SETOF RECORD AS

The TABLE syntax is a bit more standards-compliant maybe, but it's not
offering any actual new functionality.

it should minimalize columns and variables collision (for beginer
users).There isn't new functionality, but it can be more accessible
for new users. What I know, current syntax is for some people curios .

Regars
Pavel Stehule

Tom Lane wrote:

"D. Dante Lorenso" <dante@lorenso.com> writes:

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS

You realize of course that you can do this *today* if you use OUT
parameters?

No, I didn't realize. I always assumed OUT parameters were like return
values from a function ... like:

(out1, out2, out3) = somefunction (in1, in2, in3);

I never realized you could return a SETOF those OUT parameters. I guess
it wasn't intuitive, but I'm learning this now.

I think all the functionality I want DOES already exist. Let me go work
with it. Thanks.

-- Dante