Thread: function language type?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I am trying to write a function in the SQL procedural language. But when I
use type plpgsql, as the user's guide says, I get this error:

ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal and the created procedural
languages.

If I try setting the language to type sql, I get:

ERROR:  parser: parse error at or near "alias"

The only occurance of the string alias is in these four lines, which are
below a DECLARE line, which is the first line of the function:

        osec        ALIAS FOR $1;
        dsec        ALIAS FOR $2;
        who        ALIAS FOR $3;
        avoids        ALIAS FOR $4;

And yes, the function has four parameters.

Any ideas?

Ian

P.S. Please keep the CC on this message, I want replies at both mailboxes.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5fSeJfn9ub9ZE1xoRAkshAJ9zzq3oq2Qu4NsICfNJmwelA58/YgCfcFyc
KjcG5GHUjCZOeXlURbDOqk4=
=Gn4j
-----END PGP SIGNATURE-----

Ian Turner <vectro@pipeline.com> writes:
> ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
> Recognized languages are sql, C, internal and the created procedural
> languages.

plpgsql is not installed by default.  See "createlang plpgsql".

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> plpgsql is not installed by default.  See "createlang plpgsql".

OK. Now it barfs on this:

    CREATE TEMPORARY TABLE NextHopTemp (
        num        integer    REFERENCES Sectors PRIMARY KEY,
        prev        integer    REFERENCES Sectors,
        settled        boolean,
        cost        integer,
    );
    RETURN 1;

with the error 'ERROR:  copyObject: don't know how to copy 611'. Does this
mean one is not permitted to create tables (even temporaries!) in a
function?

Thanks for your advice,

Ian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5fgQcfn9ub9ZE1xoRAm/oAKDbnXiIYmAaxWKe91t0Du/UiNYUjgCggFwA
ynGoNrrbLFV8ujrU8yUUBXM=
=wNrE
-----END PGP SIGNATURE-----

Ian Turner <vectro@pipeline.com> writes:
> with the error 'ERROR:  copyObject: don't know how to copy 611'. Does this
> mean one is not permitted to create tables (even temporaries!) in a
> function?

At the moment, I think not.  There's no fundamental reason why it
couldn't be done, just some unfinished legwork (like writing a copy
subroutine for CreateStmt parse nodes...)

            regards, tom lane

Ian Turner <iant@mail.brainstorm.net> writes:
>>>> with the error 'ERROR:  copyObject: don't know how to copy 611'. Does this
>>>> mean one is not permitted to create tables (even temporaries!) in a
>>>> function?
>>
>> At the moment, I think not.  There's no fundamental reason why it
>> couldn't be done, just some unfinished legwork (like writing a copy
>> subroutine for CreateStmt parse nodes...)

> OK. How hard would this be?

Actually I think the copyObject support may be the only missing piece.
But don't quote me.

> And just out of curiosity, why is the process different if one is in a
> function?

The issue with plpgsql is it wants to prepare a saved plan for SQL
commands, so they don't have to be re-planned on every call.  So that
means copying the parser output to someplace.  A lot of utility-class
statement parsenodes aren't in copyObject's repertoire for some
reason (laziness long ago no doubt).

> Can one create tables using the perl, C, or TCL interfaces?

Offhand I think this would work out-of-the-box in pltcl and plperl,
because they don't do preplanning.  This is also why you can do
something like "SELECT ... FROM $1" in those PLs and not in plpgsql:
they just form the command as a string and then run it through the
whole parse/plan process every time.

And of course you can do anything you want in C, if you don't mind
the learning curve.

            regards, tom lane

Tom Lane wrote:
>
> > Can one create tables using the perl, C, or TCL interfaces?
>
> Offhand I think this would work out-of-the-box in pltcl and plperl,
> because they don't do preplanning.  This is also why you can do
> something like "SELECT ... FROM $1" in those PLs and not in plpgsql:
> they just form the command as a string and then run it through the
> whole parse/plan process every time.

    More  than  that.  PL/Tcl  supports  saved  plans,  but  also
    supports direct SPI query execution. So it's the decision  of
    the  function programmer, which queries to plan and save once
    and which don't.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Ian Turner <iant@mail.brainstorm.net> writes:
> Looking at the source, I see the following parsenodes which are NOT
> supported by copyObject:

Uh, what version of the source are you looking at?  Quite a few of
those *are* supported.

> Which of these is it worth supporting? I will implement the necessary
> _copy<type> functions.

The missing stuff is basically the 600-series node types; any XXXStmt
node that you want to be able to use in a plpgsql function needs to be
copiable.  If you want to support CREATE TABLE you will likely find that
some more of the 700-series nodes are also needed for CREATE TABLE
infrastructure.

It is not worth your trouble to do this unless you are working from
current sources (CVS or a recent daily snapshot)...

            regards, tom lane