Re: [GENERAL] cannot create table using pl/perl

[Michael Fuhr]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend