Thread: Creating tables from within functions
Is it possible to issue a CREATE TABLE statement from inside of a PostgreSQL function? If not, then how about from within a PL/pgSQL function? I have tried a lot of different permutations but can't seem to get it to compile and/or run cleanly. For example, consider: CREATE FUNCTION _testcreate () RETURNS text AS 'BEGIN CREATE TABLE switch_table2 (switch_id varchar(32), selection_name varchar(100));end;' language 'plpgsql'; What is wrong with this? What type should be returned? Or perhaps it is simply not possible to create a table from within a function? Please give me the reply soon as possible Thanks!
On Fri, 24 Jan 2003, Seethalakshmi VB wrote: > Is it possible to issue a CREATE TABLE statement from inside of a PostgreSQL > function? If not, then how about from within a PL/pgSQL function? I have > tried a lot of different permutations but can't seem to get it to compile > and/or run cleanly. For example, consider: > > CREATE FUNCTION _testcreate () RETURNS text AS ' > BEGIN > CREATE TABLE switch_table2 (switch_id varchar(32), > selection_name varchar(100)); > end; > ' language 'plpgsql'; > > What is wrong with this? What type should be returned? Or perhaps it is Well you have no return value. Once I put one in it works for me.
Hello, Try this: CREATE FUNCTION _testcreate () RETURNS text AS ' BEGIN CREATE TABLE switch_table2 (switch_id varchar(32), selection_name varchar(100)); RETURN ''true''; END; ' language 'plpgsql'; SELECT _testcreate(); Result: snlsor=# SELECT _testcreate();_testcreate -------------true (1 row) me=# \d List of relations Name | Type | Owner ----------------+----------+--------switch_table2 | table | me > -----Original Message----- > From: Seethalakshmi VB [SMTP:seethalakshmi.vb@majoris.com] > Sent: vrijdag 24 januari 2003 09:37 > To: pgsql-sql@postgresql.org > Subject: [SQL] Creating tables from within functions > > Is it possible to issue a CREATE TABLE statement from inside of a > PostgreSQL > function? If not, then how about from within a PL/pgSQL function? I have > tried a lot of different permutations but can't seem to get it to compile > and/or run cleanly. For example, consider: > > CREATE FUNCTION _testcreate () RETURNS text AS ' > BEGIN > CREATE TABLE switch_table2 (switch_id varchar(32), > selection_name varchar(100)); > end; > ' language 'plpgsql'; > > What is wrong with this? What type should be returned? Or perhaps it is > simply not possible to create a table from within a function? > > Please give me the reply soon as possible > Thanks! > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster