function language type?

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