By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Writing DDLs in Functions

P: 4
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
Dec 15 '06 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 534
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION foo() RETURNS text AS '
  2. BEGIN
  3.     create table bar (
  4.         field1 int not null,
  5.         field2 varchar(512)
  6.     );
  7.  
  8.     return ''OK'';
  9.  
  10. END;
  11. ' LANGUAGE plpgsql
  12. ;
  13. -- ---------------------------
  14. CREATE OR REPLACE FUNCTION bar() RETURNS name AS '
  15.  
  16.     create table foo (
  17.         id  serial  NOT NULL,
  18.         description varchar(512)
  19.     );
  20.  
  21.     select tablename from pg_tables where tablename = ''foo'';
  22.  
  23. ' LANGUAGE SQL
  24. ;
  25.  
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.
Dec 17 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.