Re: create a table inside a function

I'm confused now because if i try my SQL command alone it works without any
problem, but in the following pglSQL code, it does not :-(

IF (outResult = 1) THEN
> return true;
> ELSE
> EXECUTE 'create table sw.tmp_import (id serial NOT NULL CONSTRAINT id_key
> PRIMARY KEY, software VARCHAR(1024), barcode VARCHAR(10), username
> VARCHAR(1024), area VARCHAR(512), locality VARCHAR(512)) WITH
> (OIDS=FALSE);';
> EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit';
> return false;
> END IF;
>

why ?

thx.

A.

On Thu, Jun 25, 2009 at 3:09 PM, Alain Roger <raf(dot)news(at)gmail(dot)com> wrote:

> This is what i did at the beginning and it did not work. But now i've just
> discovered that my create table command was wrong...so it's ok. :-)
> thx.
>
> A.
>
>
> On Thu, Jun 25, 2009 at 3:01 PM, Frank Heikens <frankygoestorio(at)mac(dot)com>wrote:
>
>> A very simple and incomplete example:
>>
>> CREATE OR REPLACE FUNCTION new_table(int) returns bool AS
>> $$
>> BEGIN
>> IF $1 = 1 THEN
>> EXECUTE 'CREATE TABLE x()';
>> ELSIF $1 = 2 THEN
>> EXECUTE 'CREATE TABLE y()';
>> ELSE
>> EXECUTE 'CREATE TABLE z()';
>> END IF;
>>
>> RETURN TRUE;
>> END;
>> $$
>> language plpgsql;
>>
>> SELECT new_table(1);
>>
>>
>>
>> Use EXECUTE and be sure you can't be the next victim of SQL injection. If
>> you need some userinput in the EXECUTE-statement, use quote_literal() and/or
>> quote_ident().
>>
>> Regards,
>> Frank
>>
>> Op 25 jun 2009, om 14:53 heeft Alain Roger het volgende geschreven:
>>
>>
>> Hi,
>>>
>>> i would like to execute the following SQL command into a function based
>>> on some IF, END IF tests before.
>>> how can i do that ?
>>>
>>> here is my SQL command:
>>> create table sw.tmp_import
>>> (
>>> id serial NOT NULL,
>>> software VARCHAR(1024),
>>> barcode VARCHAR(10),
>>> username VARCHAR(1024),
>>> area VARCHAR(512),
>>> locality VARCHAR(512)
>>> CONSTRAINT id_pkey PRIMARY KEY (id))
>>> WITH (OIDS=FALSE);
>>>
>>> thanks a lot,
>>>
>>> Alain
>>> -----------------------------------------------------------
>>> Windows XP x64 SP2 / Fedora 10 KDE 4.2
>>> PostgreSQL 8.3.5 / MS SQL server 2005
>>> Apache 2.2.10
>>> PHP 5.2.6
>>> C# 2005-2008
>>>
>>
>>
>
>
> --
> Alain
> -----------------------------------------------------------
> Windows XP x64 SP2 / Fedora 10 KDE 4.2
> PostgreSQL 8.3.5 / MS SQL server 2005
> Apache 2.2.10
> PHP 5.2.6
> C# 2005-2008
>

--
Alain
-----------------------------------------------------------
Windows XP x64 SP2 / Fedora 10 KDE 4.2
PostgreSQL 8.3.5 / MS SQL server 2005
Apache 2.2.10
PHP 5.2.6
C# 2005-2008