pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate table in functions
10+ messages / 6 participants
[nested] [flat]
* Create table in functions
@ 2000-08-23 14:49 Andreas Tille <tillea@rki.de>
0 siblings, 3 replies; 10+ messages in thread
From: Andreas Tille @ 2000-08-23 14:49 UTC (permalink / raw)
To: PostgreSQL SQL <pgsql-sql@hub.org>
Hello,
I striped down my function up to a strange thing:
web=# create function pHelpTable( )
web-# returns int
web-# As '
web'# Begin
web'# Create Table Temp_NumTable ( Num int ) ;
web'#
web'# return 17 ;
web'# End; '
web-# language 'plpgsql' ;
CREATE
web=#
web=# select pHelpTable( );
ERROR: copyObject: don't know how to copy 611
web=#
What does this mean? The ERROR is caused by the Create Table statement
(when I removed it from my complex function it worked well).
So why doesn't this work and what copy function fails here??
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Create table in functions
@ 2000-08-23 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Tille <tillea@rki.de>
2 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2000-08-23 15:31 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Andreas Tille <tillea@rki.de> writes:
> web=# create function pHelpTable( )
> web-# returns int
> web-# As '
> web'# Begin
> web'# Create Table Temp_NumTable ( Num int ) ;
> web'#
> web'# return 17 ;
> web'# End; '
> web-# language 'plpgsql' ;
> CREATE
The majority of utility commands don't work in plpgsql functions,
because no one had gotten 'round to writing querytree copy routines
for them. Ian Turner finally did all the gruntwork for that a few
weeks ago, so this does work in current sources and will be in 7.1.
I believe you could make this work in 7.0 by using an SQL function
instead of plpgsql, if that helps any...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Create table in functions
@ 2000-08-23 16:46 Jie Liang <jliang@ipinc.com>
parent: Andreas Tille <tillea@rki.de>
2 siblings, 0 replies; 10+ messages in thread
From: Jie Liang @ 2000-08-23 16:46 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Hi, there,
I don't think that you can use DDL in PL/pgSQL, normally say , you can
only use DML in PL/pgSQL.
i.e. you can use select into,update,delete,insert ... , but you cannot use
create, alter, grant ...
Andreas Tille wrote:
> Hello,
>
> I striped down my function up to a strange thing:
>
> web=# create function pHelpTable( )
> web-# returns int
> web-# As '
> web'# Begin
> web'# Create Table Temp_NumTable ( Num int ) ;
> web'#
> web'# return 17 ;
> web'# End; '
> web-# language 'plpgsql' ;
> CREATE
> web=#
> web=# select pHelpTable( );
> ERROR: copyObject: don't know how to copy 611
> web=#
>
> What does this mean? The ERROR is caused by the Create Table statement
> (when I removed it from my complex function it worked well).
> So why doesn't this work and what copy function fails here??
>
> Kind regards
>
> Andreas.
--
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Create table in functions
@ 2000-08-24 00:57 Hiroshi Inoue <Inoue@tpf.co.jp>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Hiroshi Inoue @ 2000-08-24 00:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
> -----Original Message-----
> From: Tom Lane
>
> Andreas Tille <tillea@rki.de> writes:
> > web=# create function pHelpTable( )
> > web-# returns int
> > web-# As '
> > web'# Begin
> > web'# Create Table Temp_NumTable ( Num int ) ;
> > web'#
> > web'# return 17 ;
> > web'# End; '
> > web-# language 'plpgsql' ;
> > CREATE
>
> The majority of utility commands don't work in plpgsql functions,
> because no one had gotten 'round to writing querytree copy routines
> for them. Ian Turner finally did all the gruntwork for that a few
> weeks ago, so this does work in current sources and will be in 7.1.
>
Hmm,Andreas's original function seems to contain other statements.
If the function contains DML statements for the table Temp_Num_Table,
it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Create table in functions
@ 2000-08-24 05:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hiroshi Inoue <Inoue@tpf.co.jp>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2000-08-24 05:32 UTC (permalink / raw)
To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: Andreas Tille <tillea@rki.de>; PostgreSQL SQL <pgsql-sql@hub.org>
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
Are you thinking about plpgsql's caching of query plans (and
specifically the table OIDs stored in those plans) or is there another
issue here?
We do need to think about invalidating cached query plans when updates
happen...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Create table in functions
@ 2000-08-24 06:14 Hiroshi Inoue <Inoue@tpf.co.jp>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Hiroshi Inoue @ 2000-08-24 06:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andreas Tille <tillea@rki.de>; PostgreSQL SQL <pgsql-sql@hub.org>
> -----Original Message-----
> From: Tom Lane
>
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > Hmm,Andreas's original function seems to contain other statements.
> > If the function contains DML statements for the table Temp_Num_Table,
> > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
>
> Are you thinking about plpgsql's caching of query plans (and
> specifically the table OIDs stored in those plans)
Yes.
We can create a table and insert into it in plpgsql functions if we
call such functions once per session(connection).
Would it be the spec of plpgsql functions in 7.1 ?
Regards.
Hiroshi Inoue
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Create table in functions
@ 2000-08-24 09:36 hlefebvre <hlefebvre@lexbase.net>
parent: Andreas Tille <tillea@rki.de>
2 siblings, 0 replies; 10+ messages in thread
From: hlefebvre @ 2000-08-24 09:36 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Andreas Tille wrote:
> What does this mean? The ERROR is caused by the Create Table statement
> (when I removed it from my complex function it worked well).
> So why doesn't this work and what copy function fails here??
Maybe you can create your table using a select into statement :
something like :
SELECT int4(1) as Num INTO Temp_NumTable WHERE 1=2;
Should create your table without CREATE statement.
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Create table in functions
@ 2000-08-25 07:32 Andreas Tille <tillea@rki.de>
parent: Hiroshi Inoue <Inoue@tpf.co.jp>
1 sibling, 1 reply; 10+ messages in thread
From: Andreas Tille @ 2000-08-25 07:32 UTC (permalink / raw)
To: ; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
That's the problem. I'm in the process of porting a set of about
30 Stroed Procedures from MS-SQL server to PostgreSQL and have
just managed only 3 :-( because of some "This is not possible with
PostgreSQL".
To make clear what I want to do, I just copy the original stored
procedure, which might be not so clever so that it would be possible
to go without the table creation:
CREATE Procedure TilleA.pHelpCountNames
( @Arbeitsgruppeshort varchar(255) ,
@Condition varchar(255)
)
/* Count names in table Mitarbeiter which belong to the group
Arbeitsgruppeshort and match the condition @Condition
*/
As
Declare @Query varchar(1024)
Declare @num int
Select @num = 0
if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
Select @ArbeitsgruppeShort = ''
end else begin
Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' + @ArbeitsgruppeShort + ''''
end
Create Table #NumTable
(
Num integer
)
Select @Query =
'Insert Into #NumTable (Num) ' +
'SELECT Count (*) ' +
'FROM Mitarbeiter m ' +
'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter = t.IdMitarbeiter ' +
'INNER JOIN Arbeitsgruppe a ON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' +
'WHERE ' + @Condition + @ArbeitsgruppeShort
Exec (@Query)
Select @num=(Select Max(Num) From #NumTable)
Drop Table #NumTable
return @num
May be there are some clever ways to avoid the temporary table.
I really wonder if my solution is in fact very clever because I'm unsure
if it is thread-safe.
Any hints?
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: RE: Create table in functions
@ 2000-09-01 02:36 Jan Wieck <janwieck@Yahoo.com>
parent: Andreas Tille <tillea@rki.de>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2000-09-01 02:36 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-sql
Andreas Tille wrote:
> On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
>
> > Hmm,Andreas's original function seems to contain other statements.
> > If the function contains DML statements for the table Temp_Num_Table,
> > it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
> That's the problem. I'm in the process of porting a set of about
> 30 Stroed Procedures from MS-SQL server to PostgreSQL and have
> just managed only 3 :-( because of some "This is not possible with
> PostgreSQL".
Could you try out the current CVS sources?
I've added an EXECUTE keyword to PL/pgSQL earlier - er -
yesterday. It should give you the power to at least port the
proc below to PostgreSQL.
> To make clear what I want to do, I just copy the original stored
> procedure, which might be not so clever so that it would be possible
> to go without the table creation:
Looks like the only purpose of the (temp) table is to hold
exactly one single row of one attribute - the result of the
count(*). From the code I guess that MS-SQL's only way to
build up a dynamic query is to put it as a string into a
variable and call Exec(@var). Maybe it's impossible to get a
return value out of that at the same time, so the only
solution is to throw it into a temp table and pick it up
again.
But anyway, the dynamic part of building the qualification
(WHERE clause) was the initial problem. And that should be
solved in CURRENT PostgreSQL sources (and get shipped with
7.1).
Read on below...
>
>
> CREATE Procedure TilleA.pHelpCountNames
> ( @Arbeitsgruppeshort varchar(255) ,
> @Condition varchar(255)
> )
> /* Count names in table Mitarbeiter which belong to the group
> Arbeitsgruppeshort and match the condition @Condition
> */
> As
> Declare @Query varchar(1024)
> Declare @num int
> Select @num = 0
>
> if @ArbeitsgruppeShort is NULL or @ArbeitsGruppeShort = '' begin
> Select @ArbeitsgruppeShort = ''
> end else begin
> Select @ArbeitsgruppeShort = ' AND a.ArbeitsgruppeShort = ' + '''' + @ArbeitsgruppeShort + ''''
> end
>
> Create Table #NumTable
> (
> Num integer
> )
>
> Select @Query =
> 'Insert Into #NumTable (Num) ' +
> 'SELECT Count (*) ' +
> 'FROM Mitarbeiter m ' +
> 'INNER JOIN tm2nMitarbeiterArbeitsgruppe t ON m.IdMitarbeiter = t.IdMitarbeiter ' +
> 'INNER JOIN Arbeitsgruppe a ON t.IdArbeitsgruppe = a.IdArbeitsgruppe ' +
> 'WHERE ' + @Condition + @ArbeitsgruppeShort
> Exec (@Query)
>
> Select @num=(Select Max(Num) From #NumTable)
>
> Drop Table #NumTable
>
> return @num
>
>
> May be there are some clever ways to avoid the temporary table.
> I really wonder if my solution is in fact very clever because I'm unsure
> if it is thread-safe.
>
> Any hints?
Let me give it a try:
CREATE FUNCTION pHelpCountNames (text, text) RETURNS integer AS
'
DECLARE
ag_short ALIAS FOR $1;
condition ALIAS FOR $2;
query text;
rec record;
result integer;
BEGIN
query := ''SELECT count(*) AS n FROM Mitarbeiter m,
tm2nMistarbeiterArbeitsgruppe t,
Arbeitsgruppe a
WHERE m.IdMitarbeiter = t.IdMitarbeiter
AND t.IdArbeitsgruppe = a.IdArbeitsgruppe'';
IF condition != '''' THEN
query := query || '' AND '' || condition;
END IF;
IF ag_short != '''' THEN
query := query || '' AND a.ArbeitsgruppeShort = '''''' ||
ag_short || '''''''';
END IF;
FOR rec IN EXECUTE query LOOP
result = rec.n;
END LOOP;
RETURN result;
END;
' LANGUAGE 'plpgsql';
Just written down. Too lazy to test it right now.
BTW: It is not strictly neccessary to build up the
querystring for EXECUTE in a variable. Any expression
resulting in a string (a function call or a concatenation of
strings, variables and functions) is allowed.
EXECUTE outside of a FOR statement simply executes the query
and that should not be a SELECT. This can be a utility
statement as well, so feel free to create and drop "temp"
tables as needed. Used as above (inside the FOR) it must be a
SELECT statement. I haven't checked if rows found, because
that count(*) query is guaranteed to return either one row,
or bomb out with a transaction abort. Usually someone should
check.
Enjoy.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: RE: Create table in functions
@ 2000-09-01 12:44 Andreas Tille <tillea@rki.de>
parent: Jan Wieck <janwieck@Yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Andreas Tille @ 2000-09-01 12:44 UTC (permalink / raw)
To: Jan Wieck <janwieck@Yahoo.com>; +Cc: pgsql-sql
On Thu, 31 Aug 2000, Jan Wieck wrote:
First of all thank you very much for your effort!
This is definitely a good argument for PostgeSQL to have such fast
support in adding features which might convince some of my collegues
or my boss :).
> Could you try out the current CVS sources?
Well, I walked around the problem and I'm affrais I don't want to use
the CVS tree in a production environment. Moreover I have very poor
bandwith and I'm sitting behind a CVS disabled firewall :(. So I
have problems to check it soon. May be I coudl try a patch against
7.0.2 sources if this would not make much trouble.
I definitely will honor your effort by testing it but I can't do this
in the next weeks!
> I've added an EXECUTE keyword to PL/pgSQL earlier - er -
> yesterday. It should give you the power to at least port the
> proc below to PostgreSQL.
Hopefully some other PostgreSQL users besides me will love you for that .
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-09-01 12:44 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-23 14:49 Create table in functions Andreas Tille <tillea@rki.de>
2000-08-23 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-24 00:57 ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-08-24 05:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-24 06:14 ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-08-25 07:32 ` Andreas Tille <tillea@rki.de>
2000-09-01 02:36 ` Jan Wieck <janwieck@Yahoo.com>
2000-09-01 12:44 ` Andreas Tille <tillea@rki.de>
2000-08-23 16:46 ` Jie Liang <jliang@ipinc.com>
2000-08-24 09:36 ` hlefebvre <hlefebvre@lexbase.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox