Re: [PATCHES] return can contains any row or record functions
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 > >To: Pavel Stehule <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED], 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 > > EnterpriseDBhttp://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 EnterpriseDBhttp://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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] return can contains any row or record functions
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 > >To: Pavel Stehule <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED], 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 > > EnterpriseDBhttp://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 EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] return can contains any row or record functions
Not from my side Regards Pavel Stehule From: Bruce Momjian To: Pavel Stehule <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], 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 EnterpriseDBhttp://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
Re: [PATCHES] return can contains any row or record functions
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 EnterpriseDBhttp://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
Re: [PATCHES] return can contains any row or record functions
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] return can contains any row or record 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 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
Re: [PATCHES] return can contains any row or record functions
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] return can contains any row or record functions
From: Neil Conway <[EMAIL PROTECTED]> To: Pavel Stehule <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] return can contains any row or record functions
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] return can contains any row or record functions
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings