Pavel Stehule wrote:
Hello
2008/5/10 D. Dante Lorenso <dante@xxxxxxxxxxxxx>:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
