Обсуждение: Create table doesn't work in plpgsql

Hi,

can I do some table manipulation in plpgsql?
Look at only the "create table" line and the error message:

create function plural (text) returns text as '  begin   create table tmp (num int4);   return $1 || ''s'';   end;'
language'plpgsql'; 
 
select plural('test'); 
CREATE
ERROR:  copyObject: don't know how to copy 611

What does the error message mean? Where can I read more about it?

Cheers, Volker

Hi,there,

I don't think you can use DDL(data definition language) in PL/SQL.
create table is not DML(data munipulation language) instead
it's a DDL. 

Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Tue, 19 Dec 2000, Volker Paul wrote:

> Hi,
> 
> can I do some table manipulation in plpgsql?
> Look at only the "create table" line and the error message:
> 
> create function plural (text) returns text as '
>    begin 
>    create table tmp (num int4); 
>    return $1 || ''s''; 
>    end;' language 'plpgsql'; 
> select plural('test'); 
> CREATE
> ERROR:  copyObject: don't know how to copy 611
> 
> What does the error message mean? Where can I read more about it?
> 
> Cheers, Volker
> 

Hi,

> I don't think you can use DDL(data definition language) in PL/SQL.
> create table is not DML(data munipulation language) instead
> it's a DDL.
Thanks, but that leaves me with a problem.
What I really want to do is something like
select str from person where id=1234;
where str is a string that contains an expression like 
famname || ', ' || givname
i.e. the final select is 
select famname || ', ' || givname from person where id=1234;
I know it's possible by building the select e.g. in bash
and calling psql with it as an argument, but do you see a possibility
that is closer to Postgres, e.g. in plpgsql?


Volker Paul

On Thu, Dec 21, 2000 at 11:51:38AM +0100, Volker Paul wrote:
> select famname || ', ' || givname from person where id=1234;
> I know it's possible by building the select e.g. in bash
> and calling psql with it as an argument, but do you see a possibility
> that is closer to Postgres, e.g. in plpgsql?

Can this be done using tcl or perl?

mrc
--       Mike Castle       Life is like a clock:  You can work constantly dalgoda@ix.netcom.com  and be right all the
time,or not work at all
 
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc   We are all of us living in the shadow of
Manhattan. -- Watchmen
 

Hi,there,

I am not quite sure what you try to do.
However, plpgsql allows you use any DDL, most of functions defined and
sql operators.
I don't see the problem.

Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Thu, 21 Dec 2000, Volker Paul wrote:

> Hi,
> 
> > I don't think you can use DDL(data definition language) in PL/SQL.
> > create table is not DML(data munipulation language) instead
> > it's a DDL.
> Thanks, but that leaves me with a problem.
> What I really want to do is something like
> select str from person where id=1234;
> where str is a string that contains an expression like 
> famname || ', ' || givname
> i.e. the final select is 
> select famname || ', ' || givname from person where id=1234;
> I know it's possible by building the select e.g. in bash
> and calling psql with it as an argument, but do you see a possibility
> that is closer to Postgres, e.g. in plpgsql?
> 
> 
> Volker Paul
> 

> Can this be done using tcl or perl?

I'll try them and report what I find out.

V.Paul

I believe (although I haven't tried it) that pltcl will allow
you to do things such as this.

On Thu, 21 Dec 2000, Volker Paul wrote:

> Hi,
> 
> > I don't think you can use DDL(data definition language) in PL/SQL.
> > create table is not DML(data munipulation language) instead
> > it's a DDL.
> Thanks, but that leaves me with a problem.
> What I really want to do is something like
> select str from person where id=1234;
> where str is a string that contains an expression like 
> famname || ', ' || givname
> i.e. the final select is 
> select famname || ', ' || givname from person where id=1234;
> I know it's possible by building the select e.g. in bash
> and calling psql with it as an argument, but do you see a possibility
> that is closer to Postgres, e.g. in plpgsql?

I believe a couple of months back... a EXECUTE command was added to plpgsql 
to allow users to dynamic sql statements.

So if you get the current development version you should be able to call

EXECUTE CREATE TABLE ....

or whatever sql you like. (I think this is still not in 7.03, not sure though)

I'm not sure about the exact syntax, but if you look in past threads for 
"execute" I'm sure you'll find it.

Have fun.

Merry Xmas.
Keith.

At 02:00 PM 22/12/2000 +0100, Volker Paul wrote:
> > Can this be done using tcl or perl?
>
>I'll try them and report what I find out.
>
>V.Paul