Re: cannot create table using pl/perl

On Sat, Aug 12, 2006 at 02:04:42PM -0700, Jim Bryan wrote:
> Installed latest postgresql 8 on windows xp,
> everything works fine with pl/pgsql; problems creating
> a table with perl however:
>
> CREATE OR REPLACE FUNCTION datetable() RETURNS integer
> AS $$
> CREATE TABLE tabletest
> (
> dayofmonth int4,
> monthofyear int4,
> theyear int4
> )
> $$
> LANGUAGE 'plperlu' ;

That function body isn't Perl code; to execute SQL commands you'll
need to use spi_exec_query or spi_query(). Here's an example that
uses a here-document; note also "RETURNS void" since the function
doesn't return anything useful:

CREATE OR REPLACE FUNCTION datetable() RETURNS void AS $$
spi_exec_query(<<END_OF_SQL);
CREATE TABLE tabletest
(
dayofmonth int4,
monthofyear int4,
theyear int4
)
END_OF_SQL
$$
LANGUAGE plperl;

--
Michael Fuhr