Обсуждение: 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