Horton

Thread

  1. Composite Types and Function Parameters

    Greg <grigorey@yahoo.co.uk> — 2010-10-25T15:56:56Z

    Hi guys, got across an interesting problem of passing params to a function in postgre: is it possible to pass a composite parameter to a function without declaring a type first? 

For example:

// declare a function
create function TEST ( object??? )
   ....
   object???.paramName    // using parameter
   ....

// calling
perform TEST( ROW(string, int, bool, etc...) )

Or do I have to do the type declaration for that parameter?

Thanks!

  2. Re: Composite Types and Function Parameters

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-10-25T16:46:47Z

    Hello

I am thinking, so it isn't possible. There are a general datatype
anyelement, but it cannot accept a second general type record.

CREATE TYPE p AS (a text, b int, c bool);

CREATE OR REPLACE FUNCTION fp(p)
RETURNS int AS $$
BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b;
END;
$$ LANGUAGE plpgsql;

postgres=# select fp(row('hello',10, false));
NOTICE:  a = hello
 fp
────
 10
(1 row)

Regards

Pavel Stehule

Time: 0.814 ms
postgres=# select fp(row('hello',10, false));


2010/10/25 Greg <grigorey@yahoo.co.uk>

> Hi guys, got across an interesting problem of passing params to a function
> in postgre: is it possible to pass a composite parameter to a function
> without declaring a type first?
>
> For example:
>
> // declare a function
> create function TEST ( object??? )
>    ....
>    object???.paramName    // using parameter
>    ....
>
> // calling
> perform TEST( ROW(string, int, bool, etc...) )
>
> Or do I have to do the type declaration for that parameter?
>
> Thanks!
>
>

  3. Re: Composite Types and Function Parameters

    Greg <grigorey@yahoo.co.uk> — 2010-10-25T22:38:23Z

    Hi Pavel, thanks! Yeah, thats what I though. I have to have a custom type or a 
very ugly looking solution for passing the params then.

To Postgre dev. team: If anyone who involved in Postgre development reading 
this, just a feature suggestion: allow array that 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.

Thanks!




________________________________
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Greg <grigorey@yahoo.co.uk>
Cc: pgsql-hackers@postgresql.org
Sent: Mon, 25 October, 2010 17:46:47
Subject: Re: [HACKERS] Composite Types and Function Parameters

Hello

I am thinking, so it isn't possible. There are a general datatype anyelement, 
but it cannot accept a second general type record.

CREATE TYPE p AS (a text, b int, c bool);

CREATE OR REPLACE FUNCTION fp(p)
RETURNS int AS $$ 
BEGIN RAISE NOTICE 'a = %', $1.a; RETURN $1.b;
END;
$$ LANGUAGE plpgsql;

postgres=# select fp(row('hello',10, false));
NOTICE:  a = hello
 fp 
────
 10
(1 row)

Regards

Pavel Stehule

Time: 0.814 ms
postgres=# select fp(row('hello',10, false));



2010/10/25 Greg <grigorey@yahoo.co.uk>

Hi guys, got across an interesting problem of passing params to a function in 
postgre: is it possible to pass a composite parameter to a function without 
declaring a type first? 

>
>For example:
>
>// declare a function
>create function TEST ( object??? )
>   ....
>   object???.paramName    // using parameter
>   ....
>
>// calling
>perform TEST( ROW(string, int, bool, etc...) )
>
>Or do I have to do the type declaration for that parameter?
>
>Thanks!
> 
>

  4. Re: Composite Types and Function Parameters

    Merlin Moncure <mmoncure@gmail.com> — 2010-10-25T22:52:55Z

    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 the params then.
>
> To Postgre dev. team: If anyone who involved in Postgre development reading this, just a feature suggestion: allow array that 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

  5. Re: Composite Types and Function Parameters

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-10-25T23:12:58Z

    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

  6. Re: Composite Types and Function Parameters

    David Wheeler <david@kineticode.com> — 2010-10-26T01:02:04Z

    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

  7. Re: Composite Types and Function Parameters

    Andrew Dunstan <andrew@dunslane.net> — 2010-10-26T01:32:26Z

    
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

  8. Re: Composite Types and Function Parameters

    Greg <grigorey@yahoo.co.uk> — 2010-10-26T08:24:54Z

    Hi Merlin, I completely forgot about hstore! I'll give it a go. Thanks!





________________________________
From: Merlin Moncure <mmoncure@gmail.com>
To: Greg <grigorey@yahoo.co.uk>
Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-hackers@postgresql.org
Sent: Mon, 25 October, 2010 23:52:55
Subject: Re: [HACKERS] Composite Types and Function Parameters

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 the params then.
>
> To Postgre dev. team: If anyone who involved in Postgre development reading 
>this, just a feature suggestion: allow array that 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

  9. Re: Composite Types and Function Parameters

    Andrew Dunstan <adunstan@postgresql.org> — 2010-10-28T03:00:16Z

      On 10/25/2010 09:32 PM, Andrew Dunstan wrote:
>
>
> 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.

A naive implementation turns out to be really trivial. It's about two 
lines, and we can then do:

    andrew=# create function rfunc (x record) returns text language
    plperlu as $$ use Data::Dumper; return Dumper(shift); $$;
    CREATE FUNCTION
    andrew=# select rfunc(row(c.relname,n.nspname)) from pg_class c join
    pg_namespace n on c.relnamespace = n.oid limit 1;
                     rfunc
    --------------------------------------
      $VAR1 = '(pg_statistic,pg_catalog)';+


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.

cheers

andrew

  10. Re: Composite Types and Function Parameters

    Andrew Dunstan <andrew@dunslane.net> — 2010-10-28T03:02:01Z

      On 10/25/2010 09:32 PM, Andrew Dunstan wrote:
>
>
> 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.

A naive implementation turns out to be really trivial. It's about two 
lines, and we can then do:

    andrew=# create function rfunc (x record) returns text language
    plperlu as $$ use Data::Dumper; return Dumper(shift); $$;
    CREATE FUNCTION
    andrew=# select rfunc(row(c.relname,n.nspname)) from pg_class c join
    pg_namespace n on c.relnamespace = n.oid limit 1;
                     rfunc
    --------------------------------------
      $VAR1 = '(pg_statistic,pg_catalog)';+


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.

cheers

andrew

  11. Re: Composite Types and Function Parameters

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-10-28T03:38:23Z

    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

  12. Re: Composite Types and Function Parameters

    Andrew Dunstan <andrew@dunslane.net> — 2010-10-28T04:08:39Z

    
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

  13. Re: Composite Types and Function Parameters

    David Wheeler <david@kineticode.com> — 2010-10-28T16:23:28Z

    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 on 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 function as 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.

Best,

David

  14. Re: Composite Types and Function Parameters

    Andrew Dunstan <andrew@dunslane.net> — 2010-10-28T16:31:50Z

    
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 infrastructure on 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 function as 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

  15. Re: Composite Types and Function Parameters

    David Wheeler <david@kineticode.com> — 2010-10-28T17:14:21Z

    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 should be done, though.

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

Best,

David