Обсуждение: create a table inside a function

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

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.




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




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

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