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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
>

--
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...2/msg00318.php
http://archives.postgresql.org/pgsql...2/msg00216.php
http://archives.postgresql.org/pgsql...2/msg00341.php
http://archives.postgresql.org/pgsql...9/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
>>
>


--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general