Обсуждение: 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.