Re: [SQL] Continuous inserts...
Hi! At 14:16 22.08.00 -0400, you wrote: >Jan Wieck <[EMAIL PROTECTED]> writes: > > I haven't looked at the code, but pg_class only has a boolean > > telling if a class has rules or not. Could it be that adding > > more rules (or dropping just a few instead of all) doesn't > > update the pg_class tuple, thus the syscache for the table > > isn't invalidated and other backends continue to use the old > > information instead of rescanning pg_rewrite? > >This is done correctly in current sources --- see >setRelhasrulesInRelation(). However I recall having dorked with that >code not long ago, and I forget what it looked like before. Perhaps >7.0.* is broken in this respect? Would think people would have noticed, >though. > > regards, tom lane Perhaps I should have mentioned that the test was done using 6.5.3 - I didn't come around to upgrade yet... Oh, yes, the mentioned rule was the ONLY rule on that database. Greetings, Joerg -- +-- Science & Engineering Applications GmbH --+ | | | Joerg Hessdoerfer | | Leading SW developer Phone:+49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 KoelnInternet: [EMAIL PROTECTED] | |http://www.sea-gmbh.com| +---+
[SQL] Using SETOF in plpgsql function
Hello, I'd like to return a set of integer in an pl/pgsql function. How can I do that ? I've tried things like that, put I've an error when executing : CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID setof INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' BEGIN select a into id from foo; return select a from foo; END; Any help is welcomed. Thanks.
[SQL] Date of creation and of change
Hello, could someone enlighten a fairly beginner how to define columns of a table with the following features: CreateDate DEFAULT value should store current date and time ChangeDate DEFAULT value at creation as above and a Trigger function which stores data end time of any change to the data set I hope someone has this quite usual feature handy or at least a pointer where this is described. Thank you very much Andreas.
Re: [SQL] Date of creation and of change
Andreas Tille wrote:
>
> Hello,
>
> could someone enlighten a fairly beginner how to define columns
> of a table with the following features:
>
>CreateDate DEFAULT value should store current date and time
create table mytable( CreateDate timestamp default timestamp('now'),
);
>ChangeDate DEFAULT value at creation as above and a Trigger
> function which stores data end time of any change
> to the data set
must be something like that :
CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS
BEGIN
ChangeDate := timestamp(''now'');
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW EXECUTE PROCEDURE myt_stamp();
> I hope someone has this quite usual feature handy or at least a
> pointer where this is described.
>
> Thank you very much
>
>Andreas.
Re: [SQL] Null function parameters
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> "Graham Vickrage" <[EMAIL PROTECTED]> writes: >> However my understanding was that if the default value is SQL NULL then any >> values passed into the function that are null would be treated as 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If you TL> declared the table column as NOT NULL then Postgres is doing exactly TL> what it should. You may wish to code the insert along the lines of TL> INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) TL> COALESCE is a handy notation for "value1 unless it's NULL, in which case TL> value2". TL> regards, tom lane But unfortunately we have no answer for primary question: | Why if we pass to function ONLY ONE null agument all the oters| | argumenta in function's boby are null too?| | Or: is it possible to pass null arguments into plpgsql functions? | Example. create function a(int, int) returns int as ' begin raise notice ''1: % 2: %'', $1, $2; if $1 is null then return $2; end if; return $1; end; ' language 'plpgsql'; tolik=# select a(1,2); NOTICE: 1: 1 2: 2 a --- 1 (1 row) tolik=# select a(null,2); NOTICE: 1: 2: a --- (1 row) -- Anatoly K. Lasareff Email: [EMAIL PROTECTED]
RE: [SQL] Using SETOF in plpgsql function
As far as i know, you can only return single values from functions at the moment. Regards Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of hlefebvre Sent: 23 August 2000 11:08 To: [EMAIL PROTECTED] Subject: [SQL] Using SETOF in plpgsql function Hello, I'd like to return a set of integer in an pl/pgsql function. How can I do that ? I've tried things like that, put I've an error when executing : CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' DECLARE ID setof INTEGER; BEGIN select a into id from foo; return ID ; END; CREATE FUNCTION SP_UPLES() RETURNS setof INTEGER AS ' BEGIN select a into id from foo; return select a from foo; END; Any help is welcomed. Thanks.
Re: [SQL] Using SETOF in plpgsql function
Graham Vickrage wrote: > > As far as i know, you can only return single values from functions at the > moment. > > Regards > > Graham Hum, this is possible a least in SQL functions. But maybe impossible in PL/PGSQL
RE: [SQL] Null function parameters
Thanks Anatoly So if I understand you correctly you can't pass more than one NULL int into a function? Therefore Newbe DBA type question: - Is this a shortcoming in postgres or is it to be expected when dealing with transactions? If it is a shotcoming are there any plans to include it in future releases? Regards Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anatoly K. Lasareff Sent: 23 August 2000 12:46 To: Tom Lane Cc: Graham Vickrage; postgresql Subject: Re: [SQL] Null function parameters > "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> "Graham Vickrage" <[EMAIL PROTECTED]> writes: >> However my understanding was that if the default value is SQL NULL then any >> values passed into the function that are null would be treated as 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If you TL> declared the table column as NOT NULL then Postgres is doing exactly TL> what it should. You may wish to code the insert along the lines of TL> INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) TL> COALESCE is a handy notation for "value1 unless it's NULL, in which case TL> value2". TL> regards, tom lane But unfortunately we have no answer for primary question: | Why if we pass to function ONLY ONE null agument all the oters| | argumenta in function's boby are null too?| | Or: is it possible to pass null arguments into plpgsql functions? | Example. create function a(int, int) returns int as ' begin raise notice ''1: % 2: %'', $1, $2; if $1 is null then return $2; end if; return $1; end; ' language 'plpgsql'; tolik=# select a(1,2); NOTICE: 1: 1 2: 2 a --- 1 (1 row) tolik=# select a(null,2); NOTICE: 1: 2: a --- (1 row) -- Anatoly K. Lasareff Email: [EMAIL PROTECTED]
Re: [SQL] Using SETOF in plpgsql function
hlefebvre wrote: > Hello, > > I'd like to return a set of integer in an pl/pgsql function. How can I > do that ? You can't. Not with PL/pgSQL nor with any other PL or C. The problem is nested deeper and requires the planned querytree redesign to get solved. Before you ask: The mentioned redesign will NOT be done for 7.1, and I'm not sure if we will be able to do it for 7.2 yet. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Null function parameters
> "GV" == Graham Vickrage <[EMAIL PROTECTED]> writes: GV> Thanks Anatoly GV> So if I understand you correctly you can't pass more than one NULL int into GV> a function? I'afraid no. My question is: if I pass one null argument into function then all other argumens, which are not null, became null inside function body. GV> Therefore Newbe DBA type question: - GV> Is this a shortcoming in postgres or is it to be expected when dealing with GV> transactions? GV> If it is a shotcoming are there any plans to include it in future releases? GV> Regards GV> Graham GV> -Original Message- GV> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On GV> Behalf Of Anatoly K. Lasareff GV> Sent: 23 August 2000 12:46 GV> To: Tom Lane GV> Cc: Graham Vickrage; postgresql GV> Subject: Re: [SQL] Null function parameters > "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> "Graham Vickrage" <[EMAIL PROTECTED]> writes: >>> However my understanding was that if the default value is SQL NULL then GV> any >>> values passed into the function that are null would be treated as GV> 'NULL'. TL> Not sure what you think you meant by that, but a null is a null. If GV> you TL> declared the table column as NOT NULL then Postgres is doing exactly TL> what it should. You may wish to code the insert along the lines of TL> INSERT INTO table VALUES (..., COALESCE($1, suitable-default), ...) TL> COALESCE is a handy notation for "value1 unless it's NULL, in which GV> case TL> value2". TL> regards, tom lane GV> But unfortunately we have no answer for primary question: GV> | Why if we pass to function ONLY ONE null agument all the oters| GV> | argumenta in function's boby are null too?| GV> | Or: is it possible to pass null arguments into plpgsql functions? | GV> Example. GV> create function a(int, int) returns int as ' GV> begin GV> raise notice ''1: % 2: %'', $1, $2; GV> if $1 is null then GV> return $2; GV> end if; GV> return $1; GV> end; GV> ' language 'plpgsql'; GV> tolik=# select a(1,2); GV> NOTICE: 1: 1 2: 2 GV> a GV> --- GV> 1 GV> (1 row) GV> tolik=# select a(null,2); GV> NOTICE: 1: 2: GV> a GV> --- GV> (1 row) -- Anatoly K. Lasareff Email: [EMAIL PROTECTED]
Re: [SQL] Using SETOF in plpgsql function
On Wed, 23 Aug 2000, Jan Wieck wrote: > Before you ask: The mentioned redesign will NOT be done for > 7.1, and I'm not sure if we will be able to do it for 7.2 > yet. I hope that 7.2 :-), my query/plan cache is still outside current interest and if core developers not will work on something like query path redesign, the query/plan cache will still out.. :-( Or already test query/plan cache anyone? IMHO it is good merge-able to current source too. Karel PS. sorry of my small sigh for this :-)
[SQL] Re: Using SETOF in plpgsql function
On Wed, 23 Aug 2000, Jan Wieck wrote: > You can't. Not with PL/pgSQL nor with any other PL or C. The > problem is nested deeper and requires the planned querytree > redesign to get solved. > > Before you ask: The mentioned redesign will NOT be done for > 7.1, and I'm not sure if we will be able to do it for 7.2 > yet. Just to make sure you've got to know that this is a feature needed by many users: Add me to the list of users who have a big need for this! Kind regards Andreas.
[SQL] Re: Date of creation and of change
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
Thanks, this works.
> CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS
> BEGIN
> ChangeDate := timestamp(''now'');
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
I tried:
web=# CREATE FUNCTION changed_at_timestamp () RETURNS OPAQUE AS '
web'# BEGIN
web'# ChangeDate := timestamp(''now'');
web'# RETURN NEW;
web'# END;
web'# ' LANGUAGE 'plpgsql';
CREATE
web=# select changed_at_timestamp () ;
ERROR: typeidTypeRelid: Invalid type - oid = 0
web=#
Is this just the wrong way to test the function?
As a beginner I try to validate each new step I do and so I wonder
if I insert the Trigger you mentioned
> CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable
> FOR EACH ROW EXECUTE PROCEDURE myt_stamp();
the function could cause errors.
Sorry, I'm not very familiar with this function stuff :-(.
Kind regards
Andreas.
[SQL] Re: Date of creation and of change
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
I've done a pg_dump and there this line was transformed to:
"createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp",
I'm afraid if I ever should use this dump as a restore the following
dates will be wrong, thought. So is there a save way to use in the
dumps?
May be the suggestion of Stuart <[EMAIL PROTECTED]>
On Wed Aug 23 14:36:56 2000
> On insert, however, this will do the job.
>
> Create table fred (joe integer, createdtime datetime not null default text
> 'now');
>
> If you dont put the text in you get the date the table was created in all
> future inserts. The text force the current now to be used.
> ?Is this fixed in 7.0.x
Would do a better job in this case?
What's wrong here?
Kind regards
Andreas.
Re: [SQL] Null function parameters
[EMAIL PROTECTED] (Anatoly K. Lasareff) writes: > I'afraid no. My question is: if I pass one null argument into function > then all other argumens, which are not null, became null inside > function body. Yes --- and not only that, but the function's result will be taken to be null whether you want it to be or not. This has been gone over *many* times before on this mail list, so I didn't think I needed to repeat it. This will be fixed in 7.1 (is already fixed in current sources). regards, tom lane
Re: [SQL] Re: Date of creation and of change
Andreas Tille wrote:
> I tried:
>
> web=# CREATE FUNCTION changed_at_timestamp () RETURNS OPAQUE AS '
> web'# BEGIN
> web'# ChangeDate := timestamp(''now'');
> web'# RETURN NEW;
> web'# END;
> web'# ' LANGUAGE 'plpgsql';
> CREATE
> web=# select changed_at_timestamp () ;
> ERROR: typeidTypeRelid: Invalid type - oid = 0
> web=#
>
> Is this just the wrong way to test the function?
Yes. The keywords NEW / OLD are available only in triggers
see
http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286
> As a beginner I try to validate each new step I do and so I wonder
> if I insert the Trigger you mentioned
>
> > CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable
> > FOR EACH ROW EXECUTE PROCEDURE myt_stamp();
>
> the function could cause errors.
>
> Sorry, I'm not very familiar with this function stuff :-(.
>
> Kind regards
>
> Andreas.
Re: [SQL] Using SETOF in plpgsql function
Jan Wieck <[EMAIL PROTECTED]> writes: > hlefebvre wrote: >> I'd like to return a set of integer in an pl/pgsql function. How can I >> do that ? > You can't. Not with PL/pgSQL nor with any other PL or C. The > problem is nested deeper and requires the planned querytree > redesign to get solved. Not really. Coincidentally enough, I am just in the middle of removing execQual.c's hard-wired assumption that only SQL-language functions can return sets. (This is a side effect of fixing the function manager so that SQL functions can be called in all contexts, eg used as index functions.) If you want to fix plpgsql so that it retains state and can produce multiple elements of a set over repeated calls, the same way that SQL functions do, then it could be done today. We may well want to rip out that whole approach to set functions later when we redo querytrees, but the real limitation so far has been bogus assumptions in the function-call API, not querytrees. regards, tom lane
Re: [SQL] Re: Date of creation and of change
Andreas Tille <[EMAIL PROTECTED]> writes:
> On Wed, 23 Aug 2000, hlefebvre wrote:
>> create table mytable( CreateDate timestamp default timestamp('now'),
>> );
> I've done a pg_dump and there this line was transformed to:
> "createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp",
This approach does not work in 7.0 (I think it did work in some prior
releases, but not recently). The recommended method is shown in the
FAQ:
4.22) How do I create a column that will default to the current time?
Use now():
CREATE TABLE test (x int, modtime timestamp default now() );
regards, tom lane
[SQL] Copy To - fixed width
Is it possible to copy from a text file that has the fields as fixed width with no delimiter to a table? The other option is a CSV file... but then how do I handle if there is a comma in one of the fields? I'm transfering information from an AS/400 ... if it is into a text file, it is fixed width, if I use Excel, it is Comma separated.
Re: [SQL] Copy To - fixed width
Sort of nevermind, can I can save it tab delimited, but I am still curious if it can be done using fixed width fields. Adam Lang Systems Engineer Rutgers Casualty Insurance Company - Original Message - From: "Adam Lang" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, August 23, 2000 10:38 AM Subject: [SQL] Copy To - fixed width > Is it possible to copy from a text file that has the fields as fixed width > with no delimiter to a table? > > The other option is a CSV file... but then how do I handle if there is a > comma in one of the fields? > > I'm transfering information from an AS/400 ... if it is into a text file, it > is fixed width, if I use Excel, it is Comma separated.
[SQL] 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.
Re: [SQL] Create table in functions
Andreas Tille <[EMAIL PROTECTED]> 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
Re: [SQL] Using SETOF in plpgsql function
Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: > > hlefebvre wrote: > >> I'd like to return a set of integer in an pl/pgsql function. How can I > >> do that ? > > > You can't. Not with PL/pgSQL nor with any other PL or C. The > > problem is nested deeper and requires the planned querytree > > redesign to get solved. > > Not really. Coincidentally enough, I am just in the middle of removing > execQual.c's hard-wired assumption that only SQL-language functions > can return sets. (This is a side effect of fixing the function manager > so that SQL functions can be called in all contexts, eg used as index > functions.) If you want to fix plpgsql so that it retains state and > can produce multiple elements of a set over repeated calls, the same > way that SQL functions do, then it could be done today. Not that easy. PL/pgSQL isn't a state machine. The precompiled code is kind of a nested tree of statements. A RETURN causes a controlled return() through all nested levels of the PL executors C calls. This might close SPI calls in execution as well. Imagine a code construct like FOR rec IN SELECT * FROM customer LOOP RETURN rec.cust_id AND RESUME; END LOOP; which would be the correct syntax for returning sets. What happens in PL/pgSQL while execution is, that at the beginning of the loop the SPI query for SELECT is performed, and then the loop executed for all rows in the SPI result set. And of course, you can have nested loops, why not. Now you want to return the first value. If you really return to the fmgr at this time, the connection to the SPI manager must be closed, loosing the result set. So how to continue later? If we want to make it now for sets of scalar values (not tuple sets), we could add another feature to the fmgr and the PL handlers, which we need later anyway. In the case of a call to a PL or C function returning a set, the fmgr creates a temp table and calls the function which fills the temp table with all the return values. Now fmgr changes the execution trees func node in a way that it is operating like an SQL function - holding a seqscan over the temp table. After the last result is returned, the temp table is removed. This'd work for tuple sets as well (so the temp table then is our tuple-source). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Create table in functions
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 [EMAIL PROTECTED] www.ipinc.com
[SQL] db benchmarks
does anyone have links to independent benchmarks
just mysql vs postgresql would be cool
the last one that came out was from Great Bridge
has been contested by many {including mysql} i was wondering if anyone
here does have independent benchmarks
id sure appreciate
[SQL] Re: Speed or configuration
Any light on this subject? The Hermit Hacker wrote:"use cut-n-paste please, and send us the results of theEXPLAIN ... stufflike the cost estimates and whatnot tell us *so* much..."This is it:exis=# \d pvdprcod NUMART | integer | not null NUMDEP | smallint | not null NUMPRO | smallint | not null MODELO | varchar(20) | not null TALLA | varchar(4) | not null COLOR | varchar(3) | not nullexis=# \d venart cvetda | smallint | not null numdep | smallint | not null numart | integer | not null mes | smallint | not null anio | integer | not null tipotr | varchar(2) | importe | float8 | cantidad | float8 |exis=# explain selectcvetda,anio,mes,sum(importe),sum(cantidad)exis-# from venart where numart inexis-# (select "NUMART" from pvdprcod where "NUMDEP"=7and "NUMPRO"=108)exis-# group by cvetda,numdep,anio,mes;NOTICE: QUERY PLAN:Aggregate (cost=79015875401357.48..79015875413208.91rows=79010 width=26) -> Group (cost=79015875401357.48..79015875409258.44rows=790095 width=26) -> Sort(cost=79015875401357.48..79015875401357.48 rows=790095width=26) -> Seq Scan on venart(cost=1.00..79015875283591.09 rows=790095width=26) SubPlan -> Materialize(cost=17942.42..17942.42 rows=34 width=4) -> Seq Scan on pvdprcod(cost=1.00..17942.42 rows=34 width=4)EXPLAINWhy Seq Scan if indexes have been created on thecolumns used for access?Best regards,Franz J Fortuny
RE: [SQL] Create table in functions
> -Original Message- > From: Tom Lane > > Andreas Tille <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
Re: [SQL] Create table in functions
"Hiroshi Inoue" <[EMAIL PROTECTED]> 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
RE: [SQL] Create table in functions
> -Original Message- > From: Tom Lane > > "Hiroshi Inoue" <[EMAIL PROTECTED]> 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
