Обсуждение: Composite Types and Function Parameters

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hi guys, got across an
interestingproblem of passing params to a function in postgre: is it possible to pass a composite parameter to a
functionwithout declaring a type first? <br /><br />For example:<br /><br />// declare a function<br />create function
TEST( object??? )<br />   ....<br />   object???.paramName    // using parameter<br />   ....<br /><br />// calling<br
/>performTEST( ROW(string, int, bool, etc...) )<br /><br />Or do I have to do the type declaration for that
parameter?<br/><br />Thanks!<br /></td></tr></table><br /> 

Hello<br /><br />I am thinking, so it isn't possible. There are a general datatype anyelement, but it cannot accept a
secondgeneral type record.<br /><br />CREATE TYPE p AS (a text, b int, c bool);<br /><br />CREATE OR REPLACE FUNCTION
fp(p)<br/> RETURNS int AS $$ <br />BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b;<br />END;<br />$$ LANGUAGE
plpgsql;<br/><br />postgres=# select fp(row('hello',10, false));<br />NOTICE:  a = hello<br /> fp <br />────<br
/> 10<br/>(1 row)<br /><br />Regards<br /><br />Pavel Stehule<br /><br />Time: 0.814 ms<br />postgres=# select
fp(row('hello',10,false));<br /><br /><br /><div class="gmail_quote">2010/10/25 Greg <span dir="ltr"><<a
href="mailto:grigorey@yahoo.co.uk">grigorey@yahoo.co.uk</a>></span><br/><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><table border="0" cellpadding="0"
cellspacing="0"><tbody><tr><tdstyle="font:inherit" valign="top">Hi guys, got across an interesting problem of passing
paramsto a function in postgre: is it possible to pass a composite parameter to a function without declaring a type
first?<br /><br />For example:<br /><br />// declare a function<br />create function TEST ( object??? )<br />   ....<br
/>  object???.paramName    // using parameter<br />   ....<br /><br />// calling<br />perform TEST( ROW(string, int,
bool,etc...) )<br /><br />Or do I have to do the type declaration for that parameter?<br /><br />Thanks!<br
/></td></tr></tbody></table><br/></blockquote></div><br /> 

On Mon, Oct 25, 2010 at 6:38 PM, Greg <grigorey@yahoo.co.uk> wrote:
>
> Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a very ugly looking solution for passing
theparams then. 
>
> To Postgre dev. team: If anyone who involved in Postgre development reading this, just a feature suggestion: allow
arraythat can accept combination of any data types to be passed to a function, for example: 
>       // declare
>       create function TEST ( anytypearray[] ) ...
>       // calling
>       perform TEST (array[bool, int, etc.] ) ....
> This would make such a nice adition to the development for postgre. Although this may be complecated to achieve.

probably hstore would be more appropriate for something like that.
You can also declare functions taking composite arrays, anyarray,
variadic array, and variadic "any", although the latter requires
function implementation in C to get the most out of it.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> probably hstore would be more appropriate for something like that.

An array is certainly completely the wrong thing if you don't intend
all the items to be the same datatype...

> You can also declare functions taking composite arrays, anyarray,
> variadic array, and variadic "any", although the latter requires
> function implementation in C to get the most out of it.

If you're willing to write in C you can already create functions that
accept type "record" (see record_out for an existence proof/coding
example).

Making plpgsql do that would be problematic though: it's not so much
the record parameter itself, as that you'd be excessively restricted
in what you can do with it.  If the column names and datatypes aren't
pretty predictable, plpgsql isn't going to be the language you want to
work in.

However, that objection doesn't hold for plperl or pltcl (and likely
not plpython, though I don't know that language enough to be sure).
So it would be a reasonable feature request to teach those PLs to
accept "record" parameters.  I think the fact that they don't stems
mostly from nobody having revisited their design since the
infrastructure that supports record_out was created.
        regards, tom lane

On Oct 25, 2010, at 4:12 PM, Tom Lane wrote:

> However, that objection doesn't hold for plperl or pltcl (and likely
> not plpython, though I don't know that language enough to be sure).
> So it would be a reasonable feature request to teach those PLs to
> accept "record" parameters.  I think the fact that they don't stems
> mostly from nobody having revisited their design since the
> infrastructure that supports record_out was created.

+1 # Would love to see that.

David

On 10/25/2010 07:12 PM, Tom Lane wrote:
> However, that objection doesn't hold for plperl or pltcl (and likely
> not plpython, though I don't know that language enough to be sure).
> So it would be a reasonable feature request to teach those PLs to
> accept "record" parameters.  I think the fact that they don't stems
> mostly from nobody having revisited their design since the
> infrastructure that supports record_out was created.

That seems like a good idea. I'll look at it for plperl.

cheers

andrew

On 10/25/2010 09:32 PM, Andrew Dunstan wrote: <blockquote cite="mid:4CC62FAA.7090505@dunslane.net" type="cite"><br
/><br/> On 10/25/2010 07:12 PM, Tom Lane wrote: <br /><blockquote type="cite">However, that objection doesn't hold for
plperlor pltcl (and likely <br /> not plpython, though I don't know that language enough to be sure). <br /> So it
wouldbe a reasonable feature request to teach those PLs to <br /> accept "record" parameters.  I think the fact that
theydon't stems <br /> mostly from nobody having revisited their design since the <br /> infrastructure that supports
record_outwas created. <br /></blockquote><br /> That seems like a good idea. I'll look at it for plperl. <br
/></blockquote><br/> A naive implementation turns out to be really trivial. It's about two lines, and we can then
do:<br/><br /><blockquote>andrew=# create function rfunc (x record) returns text language plperlu as $$ use
Data::Dumper;return Dumper(shift); $$;<br /> CREATE FUNCTION<br /> andrew=# select rfunc(row(c.relname,n.nspname)) from
pg_classc join pg_namespace n on c.relnamespace = n.oid limit 1;<br />                 rfunc                 <br />
--------------------------------------<br/>  $VAR1 = '(pg_statistic,pg_catalog)';+<br />  <br /><br /></blockquote> But
Ithink we can do better than this. We should really pass an hashref with the record's column names as keys rather than
justcalling record_out. I'll work on that.<br /><br /> cheers<br /><br /> andrew<br /> 

On 10/25/2010 09:32 PM, Andrew Dunstan wrote: <blockquote cite="mid:4CC62FAA.7090505@dunslane.net" type="cite"><br
/><br/> On 10/25/2010 07:12 PM, Tom Lane wrote: <br /><blockquote type="cite">However, that objection doesn't hold for
plperlor pltcl (and likely <br /> not plpython, though I don't know that language enough to be sure). <br /> So it
wouldbe a reasonable feature request to teach those PLs to <br /> accept "record" parameters.  I think the fact that
theydon't stems <br /> mostly from nobody having revisited their design since the <br /> infrastructure that supports
record_outwas created. <br /></blockquote><br /> That seems like a good idea. I'll look at it for plperl. <br
/></blockquote><br/> A naive implementation turns out to be really trivial. It's about two lines, and we can then
do:<br/><br /><blockquote>andrew=# create function rfunc (x record) returns text language plperlu as $$ use
Data::Dumper;return Dumper(shift); $$;<br /> CREATE FUNCTION<br /> andrew=# select rfunc(row(c.relname,n.nspname)) from
pg_classc join pg_namespace n on c.relnamespace = n.oid limit 1;<br />                 rfunc                 <br />
--------------------------------------<br/>  $VAR1 = '(pg_statistic,pg_catalog)';+<br />  <br /><br /></blockquote> But
Ithink we can do better than this. We should really pass an hashref with the record's column names as keys rather than
justcalling record_out. I'll work on that.<br /><br /> cheers<br /><br /> andrew<br /><br /> 

Andrew Dunstan <andrew@dunslane.net> writes:
> But I think we can do better than this. We should really pass an hashref 
> with the record's column names as keys rather than just calling 
> record_out. I'll work on that.

Definitely.  If you aren't providing that info then it's hard to write
a generic function, which is more or less the whole point here.  I'd
even argue that it'd be nice if the function could find out the data
types of the record's columns; though I have no idea what a reasonable
API for that would be in Perl.
        regards, tom lane

On 10/27/2010 11:38 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> But I think we can do better than this. We should really pass an hashref
>> with the record's column names as keys rather than just calling
>> record_out. I'll work on that.
> Definitely.  If you aren't providing that info then it's hard to write
> a generic function, which is more or less the whole point here.  I'd
> even argue that it'd be nice if the function could find out the data
> types of the record's columns; though I have no idea what a reasonable
> API for that would be in Perl.



Well, it turns out that the hashref required exactly one more line to
achieve. We already have all the infrastructure on the composite
handling code, and all it requires it to enable it for the RECORDOID case.

As for your idea of exposing type info, we could certainly do that using
the same mechanism we use for the trigger $_TD stuff.

Patch so far attached.

cheers

andrew

On 10/28/2010 12:23 PM, David E. Wheeler wrote:
> On Oct 27, 2010, at 9:08 PM, Andrew Dunstan wrote:
>
>> Well, it turns out that the hashref required exactly one more line to achieve. We already have all the
infrastructureon the composite handling code, and all it requires it to enable it for the RECORDOID case.
 
> I don't suppose that it would be just as easy to allow an array passed to PL/Perl to be read into the PL/Perl
functionas an array reference, would it? Since it would break backcompat, it would need to be enabled by a plperl.*
directive,but man, I would kill for that.
 

Of course it's possible, but it's a different feature. As for "just as 
easy", no, it's much more work. I agree it should be done, though.

cheers

andrew

On Oct 27, 2010, at 9:08 PM, Andrew Dunstan wrote:

> Well, it turns out that the hashref required exactly one more line to achieve. We already have all the infrastructure
onthe composite handling code, and all it requires it to enable it for the RECORDOID case. 

I don't suppose that it would be just as easy to allow an array passed to PL/Perl to be read into the PL/Perl function
asan array reference, would it? Since it would break backcompat, it would need to be enabled by a plperl.* directive,
butman, I would kill for that. 

Best,

David

On Oct 28, 2010, at 9:31 AM, Andrew Dunstan wrote:

> Of course it's possible, but it's a different feature. As for "just as easy", no, it's much more work. I agree it
shouldbe done, though. 

I bet we could raise some money to fund it's development. How much work are we talking about here?

Best,

David