Thread: 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