Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl

From: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
Cc: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>, Greg Sabino Mullane <greg(at)endpoint(dot)com>, Richard Huxton <dev(at)archonet(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Date: 2010-02-16 22:06:00
Message-ID: 20100216220600.GQ373@timac.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote:
> On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
>
> From the docs:
>
> > Immediately after the function name, in parenthesis, a comma separated list of
> > type names can be given. For example:
> >
> > 'pi()'
> > 'generate_series(int,int)'
> > 'array_cat(int[], int[])'
> > 'myschema.myfunc(date, float8)'
>
> It could also just be 'pi', no?

Yes. A vestige from when the parens were still needed. Fixed.

> > Functions with C<varadic> arguments can be called with a fixed number of
> > arguments by repeating the type name in the signature the same number of times.
>
> I assume that type names can be omitted her, too, yes?

No, it seems not. You have to either repeat the type name the right number
of times, or use '...', which simply duplicates the type name for you
behind the scenes. I'll clarify that in the docs (and fix all the
places I spelt variadic wrong :)

> > $pi = SP->pi();
> > $seqn = SP->nextval($sequence_name);
> >
> > Using this form you can't easily specify a schema name or argument types,

SP->"schema.func"() doesn't work. ($name="schema.func"; SP->$name() works.)

> > and you can't call varadic functions.
>
> Why not?

Using spi_prepare('select * from variadic_func($1)') the error is "there
is no parameter $1". I suspect calls to varadic functions do need
correct nargs and type information given to the SPI_prepare call.

> Also, I notice a few `==head`s. I think that's one too many "="s.

Fixed. Thanks.

> > For varadic functions, separate plans are created and cached for each distinct
> > number of arguments the function is called with.
>
> Why?

It keeps the code simple and repeat calls fast.

> > Functions with a varadic argument can't be called with no values for that
> > argument. You'll get a "function ... does not exist" error. This appears to be
> > a PostgreSQL limitation.
>
> Hrm. Worth enquiring about.

I found it in the docs: "A parameter marked VARIADIC matches *one* or
more occurrences of its element type".
http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html

> So, is this on GitHub yet? That way I can submit patches.

I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these
changes. It's in git but not github yet. Maybe soonish.

Tim.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2010-02-16 22:13:30 Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous Message Andrew Dunstan 2010-02-16 21:42:29 Re: CommitFest Status Summary - 2010-02-14