Обсуждение: Returning RECORD from PGSQL without custom type?
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;
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;
Just as you've allowed me to define the IN variable names without
needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned
record column names and types in a simple declaration like I show above.
Does this feature request make sense to everyone? It would make
programming set returning record functions a lot easier.
-- Dante
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 > Just as you've allowed me to define the IN variable names without needing > the legacy 'ALIAS $1 ...' format, I'd like to name the returned record > column names and types in a simple declaration like I show above. > > Does this feature request make sense to everyone? It would make programming > set returning record functions a lot easier. > > -- Dante > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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 > Regards > Pavel Stehule > >> Just as you've allowed me to define the IN variable names without needing >> the legacy 'ALIAS $1 ...' format, I'd like to name the returned record >> column names and types in a simple declaration like I show above. >> >> Does this feature request make sense to everyone? It would make programming >> set returning record functions a lot easier. >> >> -- Dante >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >
"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 > > regards, tom lane >
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 > > 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 >