Re: create a table inside a function
От | Alain Roger |
---|---|
Тема | Re: create a table inside a function |
Дата | |
Msg-id | 75645bbb0906250629r7780be7fxab8d08451d9f923e@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: create a table inside a function (Alain Roger <raf.news@gmail.com>) |
Ответы |
Re: create a table inside a function
(Adrian Klaver <aklaver@comcast.net>)
Re: create a table inside a function (Alvaro Herrera <alvherre@commandprompt.com>) |
Список | pgsql-general |
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 :-(
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
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-2008Alain
-----------------------------------------------------------
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
В списке pgsql-general по дате отправления: