create a table inside a function

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:

On Thu, Jun 25, 2009 at 02:53:59PM +0200, Alain Roger wrote:

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 ?

1. you can use execute in pl/pgsql.
2. if your ifs are basically testing if the table exists - you might
want to check this:
http://www.depesz.com/index.php/2008/06/18/conditional-ddl/

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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@mac.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

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.news@gmail.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@mac.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

On Thursday 25 June 2009 6:29:10 am Alain Roger wrote:

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 :-(

What is the error?

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);';

^ This could be part of the problem.

EXECUTE 'ALTER TABLE sw.tmp_import OWNER TO usr_audit';
return false;
END IF;

why ?

thx.

A.

--
Adrian Klaver
aklaver@comcast.net

Alain Roger escribi�:

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;

Just leave out the EXECUTE and quotes. This example should work without
them.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support