Re: Composite Types and Function Parameters

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!

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(at)yahoo(dot)co(dot)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!
>
>

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(dot)stehule(at)gmail(dot)com>
To: Greg <grigorey(at)yahoo(dot)co(dot)uk>
Cc: pgsql-hackers(at)postgresql(dot)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(at)yahoo(dot)co(dot)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!
>
>

On Mon, Oct 25, 2010 at 6:38 PM, Greg <grigorey(at)yahoo(dot)co(dot)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

Merlin Moncure <mmoncure(at)gmail(dot)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

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

________________________________
From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Greg <grigorey(at)yahoo(dot)co(dot)uk>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>; pgsql-hackers(at)postgresql(dot)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(at)yahoo(dot)co(dot)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

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

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

Andrew Dunstan <andrew(at)dunslane(dot)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(at)dunslane(dot)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 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

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

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