Thread: Create table in functions
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.
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
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
> -----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
"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
> -----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
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.
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.
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
portthe 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
shouldbe 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
autility 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
toreturn 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 #
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.