Lists: | pgsql-hackers |
---|
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | why can't plpgsql return a row-expression? |
Date: | 2012-10-08 14:42:27 |
Message-ID: | CA+TgmoZKsw_13+zBqO3QmncK=kp+7-sWuv6NsMr7Z2K_wMW52Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
PL/pgsql seems to have a strange restriction regarding "RETURN".
Normally, you can write "RETURN <expression>". But if the function
returns a row type, then you can only write "RETURN variable" or
"RETURN NULL".
rhaas=# create type xyz as (a int, b int);
CREATE TYPE
rhaas=# select row(1,2)::xyz;
row
-------
(1,2)
(1 row)
rhaas=# create or replace function return_xyz() returns xyz as $$
rhaas$# begin
rhaas$# return row(1,2)::xyz;
rhaas$# end$$ language plpgsql;
ERROR: RETURN must specify a record or row variable in function returning row
LINE 3: return row(1,2)::xyz;
^
Off the top of my head, I can't think of any reason for this
restriction, nor can I find any code comments or anything in the
commit log which explains the reason for it. Does anyone know why we
don't allow this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: why can't plpgsql return a row-expression? |
Date: | 2012-10-08 15:09:22 |
Message-ID: | 1492.1349708962@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> ERROR: RETURN must specify a record or row variable in function returning row
> Off the top of my head, I can't think of any reason for this
> restriction, nor can I find any code comments or anything in the
> commit log which explains the reason for it. Does anyone know why we
> don't allow this?
Laziness, probably. Feel free to have at it.
regards, tom lane
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: why can't plpgsql return a row-expression? |
Date: | 2012-10-08 16:21:48 |
Message-ID: | CAFj8pRDg-5d_8O=2q=s_V3uz5cp5Xu_YbhapwvFD5j1LRx3+4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2012/10/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> ERROR: RETURN must specify a record or row variable in function returning row
>
>> Off the top of my head, I can't think of any reason for this
>> restriction, nor can I find any code comments or anything in the
>> commit log which explains the reason for it. Does anyone know why we
>> don't allow this?
>
> Laziness, probably. Feel free to have at it.
I wrote patch some years ago. It was rejected from performance reasons
- because every row had to be casted to resulted type.
Pavel
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: why can't plpgsql return a row-expression? |
Date: | 2012-10-08 16:53:10 |
Message-ID: | 3567.1349715190@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2012/10/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Laziness, probably. Feel free to have at it.
> I wrote patch some years ago. It was rejected from performance reasons
> - because every row had to be casted to resulted type.
I don't recall that patch in any detail, but it's not apparent to me
that an extra cast step *must* be required to implement this. In the
cases that are supported now, surely we could notice that no additional
work is required.
It's also worth commenting that if we were to switch the storage of
composite-type plpgsql variables to HeapTuple, as has been suggested
here for other reasons, the performance tradeoffs in this area would
likely change completely anyway.
regards, tom lane
From: | Asif Rehman <asifr(dot)rehman(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: why can't plpgsql return a row-expression? |
Date: | 2012-11-12 13:50:49 |
Message-ID: | CAAuGLxWpEDfwAE6DAJMF7SxEwFUsA0f68P07RetBbpf_FSaShA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
I have tried to solve this issue. Please see the attached patch.
With this patch, any expression is allowed in the return statement. For any
invalid expression an error is generated without doing any special handling.
When a row expression is used in the return statement then the resultant
tuple will have rowtype in a single column that needed to be extracted.
Hence I have handled that case in exec_stmt_return().
any comments/suggestions?
Regards,
--Asif
On Mon, Oct 8, 2012 at 9:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2012/10/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >> Laziness, probably. Feel free to have at it.
>
> > I wrote patch some years ago. It was rejected from performance reasons
> > - because every row had to be casted to resulted type.
>
> I don't recall that patch in any detail, but it's not apparent to me
> that an extra cast step *must* be required to implement this. In the
> cases that are supported now, surely we could notice that no additional
> work is required.
>
> It's also worth commenting that if we were to switch the storage of
> composite-type plpgsql variables to HeapTuple, as has been suggested
> here for other reasons, the performance tradeoffs in this area would
> likely change completely anyway.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
Attachment | Content-Type | Size |
---|---|---|
return_rowtype.patch | application/octet-stream | 9.5 KB |