Postgres Pro
Downloads
Home   >   mailing lists

Thread: inner join problem with temporary tables

inner join problem with temporary tables

From
"guillermo arias"
Date:
PERJViBzdHlsZT0iZm9udC1mYW1pbHk6QXJpYWwsIHNhbnMtc2VyaWY7IGZv
bnQtc2l6ZToxMHB0OyI+PEZPTlQgc2l6ZT0iMiI+PFNQQU4gc3R5bGU9ImZv
bnQtZmFtaWx5OiBBcmlhbCxzYW5zLXNlcmlmOyI+SGkgcGVvcGxlLCBpIGhh
dmUgYSBwcm9ibGVtIHdpdGggaW5uZXIgam9pbiBhbmQgdGVtcG9yYXJ5IHRh
YmxlczxCUj48QlI+SSBoYXZlIDIgdGFibGVzOiBhcnRpY2xlcyBhbmQgZXhp
c3RlbmNlczxCUj48QlI+YXJ0aWNsZXMgPEJSPkNSRUFURSBUQUJMRSBwdWJs
aWMuYXJ0aWNsZXM8QlI+KDxCUj4mbmJzcDsgYXJ0X2NvZCBjaGFyYWN0ZXIg
dmFyeWluZyg1KSBOT1QgTlVMTCBERUZBVUxUICcnOjpjaGFyYWN0ZXIgdmFy
eWluZyw8QlI+Jm5ic3A7IGFydF9kZXNjcmkgY2hhcmFjdGVyIHZhcnlpbmco
MjApIERFRkFVTFQgJyc6OmNoYXJhY3RlciB2YXJ5aW5nLDxCUj4mbmJzcDsg
Q09OU1RSQUlOVCBhcnRpY2xlc19wa2V5IFBSSU1BUlkgS0VZIChhcnRfY29k
KTxCUj4pIDxCUj48QlI+IjEiOyJuYWlscyI8QlI+IjIiOyJoYW1tZXJzIjxC
Uj4iMyI7Indvb2QiPEJSPjxCUj5leGlzdGVuY2VzIDxCUj5DUkVBVEUgVEFC
TEUgcHVibGljLmV4aXN0ZW5jZXM8QlI+KDxCUj4mbmJzcDsgYXJ0X2NvZCBj
aGFyYWN0ZXIgdmFyeWluZyg1KSBERUZBVUxUICcnOjpjaGFyYWN0ZXIgdmFy
eWluZyw8QlI+Jm5ic3A7IGV4aXNfdWJpYyBjaGFyYWN0ZXIgdmFyeWluZygy
MCkgREVGQVVMVCAnJzo6Y2hhcmFjdGVyIHZhcnlpbmcsPEJSPiZuYnNwOyBl
eGlzX3F0eSBudW1lcmljKDgpIERFRkFVTFQgMDxCUj4pIDxCUj48QlI+IjEi
OyJuZXcgeW9yayI7MTAwPEJSPiIxIjsiZGFsbGFzIjsxMzA8QlI+IjIiOyJt
aWFtaSI7MTM5MDxCUj4iMyI7ImJhbHRpbW9yZSI7MzkwPEJSPiIzIjsibG91
aXNpYW5hIjsyMDxCUj48QlI+QW5kIGEgZnVuY3Rpb24gdGhhdCBpcyBkdWUg
dG8gcmVsYXRlIGJvdGggdGFibGVzIGFuZCBnaXZlIG1lIGEgbGlzdCBvZiBh
cnRpY2xlcyB3aXRoPEJSPnViaWNhdGlvbiBhbmQgcXVhbnRpdHkuPEJSPldo
YXRpIGRvIGluIHRoZSBmdW5jdGlvbiBpcyBmaXJzdCBsb2FkIDIgdGVtcG9y
YXJ5IHRhYmxlcywgdGhlbiB0aGUgaW5uZXIgam9pbi48QlI+SSBrbm93IHRo
aXMgaXMgbm8gdGhlIGJlc3Qgd2F5LCBidXQgaSB3b3VsZCBsaWtlIHRvIGtu
b3cgd2h5IGl0IGRvZXMgbm90IHdvcmsuIE5vdGljZSB0aGF0IDxCUj5pbiBt
cyBzcWwgc2VydmVyIGl0IHdvcmtzIGZpbmUuPEJSPjxCUj48QlI+Q1JFQVRF
IE9SIFJFUExBQ0UgRlVOQ1RJT04gcHVibGljLnRlc3QxIChvdXQgYXJ0X2Nv
ZCB2YXJjaGFyLG91dCBhcnRfZGVzY3JpIHZhcmNoYXIsIDxCUj5vdXQgZXhp
c191YmljIHZhcmNoYXIsIG91dCBleGlzX3F0eSBudW1lcmljKSByZXR1cm5z
IHNldG9mIHJlY29yZCBhczxCUj4kYm9keSQ8QlI+PEJSPnNlbGVjdCAqIGlu
dG8gdGVtcCB0YWJsZSB0X2FydGkgZnJvbSBwdWJsaWMuYXJ0aWNsZXM7PEJS
PnNlbGVjdCAqIGludG8gdGVtcCB0YWJsZSB0X2V4aXMgZnJvbSBwdWJsaWMu
ZXhpc3RlbmNlczs8QlI+PEJSPnNlbGVjdCBhLmFydF9jb2QsYS5hcnRfZGVz
Y3JpLGUuZXhpc191YmljLGUuZXhpc19xdHk8QlI+ZnJvbSB0X2FydGkgYSBp
bm5lciBqb2luIHRfZXhpcyBlIG9uIGEuYXJ0X2NvZD0gZS5hcnRfY29kOzxC
Uj4kYm9keSQ8QlI+Jm5ic3A7TEFOR1VBR0UgJ3NxbCcgVk9MQVRJTEU7PEJS
PjxCUj48QlI+PEJSPldoZW4gaSBjYWxsIHRoZSBmdW5jdGlvbiB3aXRoIHRo
aXMgbGluZTo8QlI+PEJSPnNlbGVjdCAqIGZyb20gbW9kZWxvLnRlc3QxKCk8
QlI+PEJSPjxCUj5UaGlzIG1lc3NhZ2UgYXBwZWFyczo8QlI+PEJSPkVSUk9S
OiByZWxhdGlvbiAidF9hcnRpIiBkb2VzIG5vdCBleGlzdDxCUj5TUUwgc3Rh
dGU6IDQyUDAxPEJSPkNvbnRleHQ6IFNRTCBmdW5jdGlvbiAidGVzdDEiPEJS
PjxCUj48QlI+V2h5IGl0IGRvZXMgbm90IHdvcms/Pz88QlI+dGhhbmtzIGZv
ciB5b3VyIGhlbHA8L1NQQU4+PC9GT05UPjxCUj4mbmJzcDs8QlI+PEhSPkdl
dCB5b3VyIEZSRUUsIExpbnV4V2F2ZXMuY29tIEVtYWlsIE5vdyEgLS0mZ3Q7
IGh0dHA6Ly93d3cuTGludXhXYXZlcy5jb208QlI+Sm9pbiBMaW51eCBEaXNj
dXNzaW9ucyEgLS0mZ3Q7IGh0dHA6Ly9Db21tdW5pdHkuTGludXhXYXZlcy5j
b208L0RJVj4=

Re: inner join problem with temporary tables

From
PFC
Date:
> This message appears:
>
> ERROR: relation "t_arti" does not exist
> SQL state: 42P01
> Context: SQL function "test1"
>
>
> Why it does not work???
> thanks for your help

    Because plpgsql functions are compiled on first execution and all queries
are then prepared. All tables are referenced directly in prepared
statements, not by name. Any prepared statement that refers to dropped
tables (even dropped temp tables) is thus unfit for consumption.

    This allows queries in plpgsql functions to be extremely fast, but it
isn't smart enough (yet) to recompile functions when a table the function
depends on is dropped.

    Just disconnect and reconnect, all prepared plans will be lost, and it
will work. Or issue your queries directly instead of using a function.

Re: inner join problem with temporary tables

From
Alvaro Herrera
Date:
PFC wrote:
>
> >This message appears:
> >
> >ERROR: relation "t_arti" does not exist
> >SQL state: 42P01
> >Context: SQL function "test1"
> >
> >
> >Why it does not work???
> >thanks for your help
>
>     Because plpgsql functions are compiled on first execution and all
>     queries  are then prepared. All tables are referenced directly in prepared
> statements, not by name. Any prepared statement that refers to dropped
> tables (even dropped temp tables) is thus unfit for consumption.

This is correct but it's not the problem at hand -- notice how the
error message is not talking about an unknown OID.  I think the problem
here is that he is using SELECT INTO, which is different in PL/pgSQL
than what is in plain SQL.  I bet using CREATE TABLE AS instead of
SELECT INTO would work.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: inner join problem with temporary tables

From
"guillermo arias"
Date:
could you please give me an example?.
How could i make an inner join select with temporary tables?

This function does not work:

REATE OR REPLACE FUNCTION modelo.test2(OUT xart_cod character varying, OUT xart_descri character varying)
  RETURNS SETOF record AS
$BODY$
begin
create temp table t_arti as (select art_cod,art_descri from modelo.articulos);
select $1,$2 from t_arti ;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


this is the error message:

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "test2" line 4 at SQL statement



_____________________________________________________________
Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.com
Join Linux Discussions! --> http://Community.LinuxWaves.com

Re: inner join problem with temporary tables

From
"Pavel Stehule"
Date:
2007/6/14, guillermo arias <guillermoariast@linuxwaves.com>:
> could you please give me an example?.
> How could i make an inner join select with temporary tables?
>
> This function does not work:
>
> REATE OR REPLACE FUNCTION modelo.test2(OUT xart_cod character varying, OUT xart_descri character varying)
>   RETURNS SETOF record AS
> $BODY$
> begin
> create temp table t_arti as (select art_cod,art_descri from modelo.articulos);
> select $1,$2 from t_arti ;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> this is the error message:
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function "test2" line 4 at SQL statement
>
>

every select's output in plpgsql have to be redirected into variables.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

regards
Pavel Stehule