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

> 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