Writing DDLs in Functions

[sarabjeethanspal]

Hii All

I need to write DDL statements in function, as we used to do in Oracle with EXECUTE IMMEDIATE command.
Is there any way we can do this.

Regards
Sarabjeet

[michaelb]

If you use SQL or PLPGSQL as language for the function you may not need to do anythinmg special. Here's a couple of oversimplified examples:

CREATE OR REPLACE FUNCTION foo() RETURNS text AS '
BEGIN
    create table bar (
        field1 int not null,
        field2 varchar(512)
    );
 
    return ''OK'';
 
END;
' LANGUAGE plpgsql
;
-- ---------------------------
CREATE OR REPLACE FUNCTION bar() RETURNS name AS '
 
    create table foo (
        id  serial  NOT NULL,
        description varchar(512)
    );
 
    select tablename from pg_tables where tablename = ''foo'';
 
' LANGUAGE SQL
;
 

However for executing dynamic commands in PL/pgSQL functions
(those that involve different tables or different data types each time they are executed) you may need to use the the EXECUTE statement.
This query string serving as an argument to EXECUTE is simply passed to the SQL engine.