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

On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:

>> Yes, IIRC, 0 == unknown as far as the server is concerned. It just
>> tells the server to resolve it when it can.
>
> An extra source of puzzlement is that the oid of the 'unknown' type is
> 705 not 0, and the unknown type isn't discussed in the docs (as far as I
> could see).

Yes, I noticed that, too. Greg, do you know the answer to that?

>>> http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html saysGuess I need to go and check the current behaviour... see below.
>>
>> And like maybe a doc patch might be useful.
>
> I would be great if someone who understood

Can any SPI experts chime in here? It seems that the ability to omit types for parameters in spi_prepare() is undocumented. Is that officially okay?

> These appear to be identical in behaviour:
>
> spi_prepare("select * from foo($1,$2)", 'unknown', 'unknown');
> spi_prepare("select * from foo($1,$2)", 'unknown')
> spi_prepare("select * from foo($1,$2)")

Ah, interesting.

> Wouldn't work unless you'd installed an AUTOLOAD function into each
> schema:: package that you wanted to use. (schema->SP::function_name()
> could be made to work but that's just too bizzare :)

Maybe SP->schema('public')->function_name()? I kind of like the idea of objects created for specific schemas, though (as in your example). Maybe that, too, is something that could be specified in the `use`statement. Or maybe `SP::schema->function`? That's kind of nice, keeps things encapsulated under SP. You could then do the identifier quoting, too. The downside is that, once loaded, the schema package names would be locked down. If I created a new schema in the connection, SP wouldn't know about it.

> Something like that is probably best. I've made PostgreSQL::PLPerl::Call
> export both &call and &SP where SP is a constant containing the name
> of a class (PostgreSQL::PLPerl::Call::SP) that just has an AUTOLOAD.

Cool, thanks!

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?

> 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?

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

Why not?

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

> You can take this approach further by specifying some of the arguments in the
> anonymous subroutine so they don't all have to be provided in the call:
>
> $some_func = sub { call('some_func(int, date[], int)', $foo, shift, $debug) };
> ...
> $val = $some_func->(\(at)dates);

Currying! :-)

> If the function was executed in scalar context then an exception will be thrown
> if more than one row is returned. For example:

Someone's going to want an iterator object/cursor. :-P

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

Why?

> 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.

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

Best,

David