return can contains any row or record functions

On Mon, 2005-07-11 at 14:58 +0100, Pavel Stehule wrote:

this patch allow using any row or record expression in return, return next
statement in row, record functions

I'll review and apply this in the next day or two.

-Neil

Neil Conway <neilc@samurai.com> writes:

On Mon, 2005-07-11 at 14:58 +0100, Pavel Stehule wrote:

this patch allow using any row or record expression in return, return next
statement in row, record functions

I'll review and apply this in the next day or two.

Seems like it's past time to pay some attention to consolidating
duplicated code in this area.

regards, tom lane

I'm confused by this part of the patch, circa line 1835 of pl_exec.c:

/* coerce type if needed */
if (estate->rsi && IsA(estate->rsi, ReturnSetInfo) &&
estate->rsi->expectedDesc != NULL &&
!compatible_tupdesc(estate->rsi->expectedDesc, in_tupdesc))
{
estate->retval = (Datum) make_tuple_from_tuple(estate,
&td,
in_tupdesc,
estate->rsi->expectedDesc);
if (estate->retval == PointerGetDatum(NULL))
ereport(...);

estate->rettupdesc = estate->rsi->expectedDesc;
}
else
{
estate->retval = (Datum) heap_copytuple(&td);
estate->rettupdesc = in_tupdesc;
}

This is for the "tuple returning function" case of RETURN. estate->rsi
seems to contain information for set-returning functions -- why do we
need to use it to obtain the expected tupledesc of the function's return
value? I'm just wondering if we ought to be fetching this from somewhere
else.

As it stands, this seems plainly wrong:

create type __trt as (x integer, y integer, z text);

create or replace function return_row4() returns __trt as $$
begin
return (1,2,'3'::text,4);
end;
$$ language plpgsql;

select return_row4();
return_row4
-------------
(1,2,3,4)
(1 row)

-Neil

From: Neil Conway <neilc@samurai.com>
To: Pavel Stehule <pavel.stehule@hotmail.com>
CC: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] return can contains any row or record functions
Date: Mon, 07 Nov 2005 18:10:13 -0500

I'm confused by this part of the patch, circa line 1835 of pl_exec.c:

/* coerce type if needed */
if (estate->rsi && IsA(estate->rsi, ReturnSetInfo) &&
estate->rsi->expectedDesc != NULL &&
!compatible_tupdesc(estate->rsi->expectedDesc, in_tupdesc))
{
estate->retval = (Datum) make_tuple_from_tuple(estate,
&td,
in_tupdesc,
estate->rsi->expectedDesc);
if (estate->retval == PointerGetDatum(NULL))
ereport(...);

estate->rettupdesc = estate->rsi->expectedDesc;
}
else
{
estate->retval = (Datum) heap_copytuple(&td);
estate->rettupdesc = in_tupdesc;
}

This is for the "tuple returning function" case of RETURN. estate->rsi
seems to contain information for set-returning functions -- why do we
need to use it to obtain the expected tupledesc of the function's return
value? I'm just wondering if we ought to be fetching this from somewhere
else.

example:
create anyfce() returns record|type ....

select anyfce0(); -- ok without conversion
select * from anyfce0() as (RSIX) --> PROBLEM1 without conversion (I have
rsix or rsiy)

create anyfce1() returns record ..
return next anyfce0()
..

select * from anyfce1() as (RSIY) --> PROBLEM2 without conversion

PL/pgSQL haven't statement for corversion, and I have to do it. I added
last this conversion when I did regress test. Generally I don't need this
variability without one exception: working with trigger variables NEW, OLD.

As it stands, this seems plainly wrong:

create type __trt as (x integer, y integer, z text);

create or replace function return_row4() returns __trt as $$
begin
return (1,2,'3'::text,4);
end;
$$ language plpgsql;

select return_row4();
return_row4
-------------
(1,2,3,4)
(1 row)

the situation is clean in this example, but when I forward any diferent row
I can have problems. When I allowed only row function or row expression ()
all was ok. But it's havy limit, and I allowed any row or record function
--> need implicit conversion. And it's very propably so return next will
contain row function, no?

Sorry for my ugly english.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

On Tue, 2005-08-11 at 08:55 +0100, Pavel Stehule wrote:

Sorry for my ugly english.

Sorry, I didn't understand your reply.

The problem is this:

- we need to find the TupleDesc of the function's expected return type
for tuple-returning functions

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I gave
before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

-Neil

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I gave
before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about desired record
type, and then I have to skip checking for a compatibility (and I can it to
do, because this is clean error, then is not necessery conversion, and next
step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's changes
about returning one field records. And maybe wee need test for conversion
everytime

create or replace function a() returns record as $$ return (1); $$ language
plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong result
type supplied in RETURN. With change return (1,2), all works fine. return
row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is necessery
caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in source, when
rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Where are we on this patch? I don't see it as applied to CVS.

---------------------------------------------------------------------------

Pavel Stehule wrote:

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I gave
before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about desired record
type, and then I have to skip checking for a compatibility (and I can it to
do, because this is clean error, then is not necessery conversion, and next
step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's changes
about returning one field records. And maybe wee need test for conversion
everytime

create or replace function a() returns record as $$ return (1); $$ language
plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong result
type supplied in RETURN. With change return (1,2), all works fine. return
row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is necessery
caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in source, when
rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Has any more work happened on this patch?

---------------------------------------------------------------------------

Pavel Stehule wrote:

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I gave
before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about desired record
type, and then I have to skip checking for a compatibility (and I can it to
do, because this is clean error, then is not necessery conversion, and next
step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's changes
about returning one field records. And maybe wee need test for conversion
everytime

create or replace function a() returns record as $$ return (1); $$ language
plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong result
type supplied in RETURN. With change return (1,2), all works fine. return
row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is necessery
caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in source, when
rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Not from my side

Regards
Pavel Stehule

From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Pavel Stehule <pavel.stehule@hotmail.com>
CC: neilc@samurai.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] return can contains any row or record functions
Date: Wed, 14 Jun 2006 18:48:00 -0400 (EDT)

Has any more work happened on this patch?

---------------------------------------------------------------------------

Pavel Stehule wrote:

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I

gave

before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about desired

record

type, and then I have to skip checking for a compatibility (and I can it

to

do, because this is clean error, then is not necessery conversion, and

next

step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's changes
about returning one field records. And maybe wee need test for

conversion

everytime

create or replace function a() returns record as $$ return (1); $$

language

plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong

result

type supplied in RETURN. With change return (1,2), all works fine.

return

row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is

necessery

caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in source,

when

rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

OK, but it seemed the patch needed more work before it could be applied.
I added a TODO item for it:

o Allow PL/RETURN to return row or record functions

http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php

---------------------------------------------------------------------------

Pavel Stehule wrote:

Not from my side

Regards
Pavel Stehule

From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Pavel Stehule <pavel.stehule@hotmail.com>
CC: neilc@samurai.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] return can contains any row or record functions
Date: Wed, 14 Jun 2006 18:48:00 -0400 (EDT)

Has any more work happened on this patch?

---------------------------------------------------------------------------

Pavel Stehule wrote:

- we can't use estate->rsi for the RETURN case, at least as presently
implemented, because that is not always filled-out -- the example I

gave

before shows how because we don't check for a compatible TupleDesc when
estate->rsi is NULL, we end up returning a value that is incompatible
with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about desired

record

type, and then I have to skip checking for a compatibility (and I can it

to

do, because this is clean error, then is not necessery conversion, and

next

step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's changes
about returning one field records. And maybe wee need test for

conversion

everytime

create or replace function a() returns record as $$ return (1); $$

language

plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong

result

type supplied in RETURN. With change return (1,2), all works fine.

return

row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of the
function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID), but
I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is

necessery

caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in source,

when

rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom has some objectives to state PL/pgSQL not explicitly into patch if I
remeber well. If I get comments, I'll correct patch promptly.

Pavel

OK, but it seemed the patch needed more work before it could be applied.
I added a TODO item for it:

o Allow PL/RETURN to return row or record functions

http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php

---------------------------------------------------------------------------

Pavel Stehule wrote:

Not from my side

Regards
Pavel Stehule

From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Pavel Stehule <pavel.stehule@hotmail.com>
CC: neilc@samurai.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] return can contains any row or record functions
Date: Wed, 14 Jun 2006 18:48:00 -0400 (EDT)

Has any more work happened on this patch?

---------------------------------------------------------------------------

Pavel Stehule wrote:

- we can't use estate->rsi for the RETURN case, at least as

presently

implemented, because that is not always filled-out -- the example I

gave

before shows how because we don't check for a compatible TupleDesc

when

estate->rsi is NULL, we end up returning a value that is

incompatible

with the function's declared return type

I expected so when rsi is NULL, then I haven't any info about

desired

record

type, and then I have to skip checking for a compatibility (and I

can it

to

do, because this is clean error, then is not necessery conversion,

and

next

step do exception and error if returned type is wrong).

I found different problem. It has maybe relation with Tom Lane's

changes

about returning one field records. And maybe wee need test for

conversion

everytime

create or replace function a() returns record as $$ return (1); $$

language

plpgsql;

is syntax clean, it's row expression, but select a() ends with wrong

result

type supplied in RETURN. With change return (1,2), all works fine.

return

row(1) works well too.

- therefore, we need to use some other way to get the TupleDesc of

the

function's declared return type. Offhand I think we can use
estate->fn_rettype (plus the funcapi stuff for handling RECORDOID),

but

I haven't had a chance to try it yet.

testing estate->fn_rettype is safer, but what is efectivity? Is

necessery

caching TupleDesc in retturn statement? I don't know? I don't study
mechanism when is rsi valid or not. But I found some samples in

source,

when

rsi was used for same purpose.

Pavel

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of

broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of

broadcast)---------------------------

TIP 4: Have you searched our list archives?

http://archives.postgresql.org

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/