inner join problem with temporary tables
<DIV style="font-family:Arial, sans-serif; font-size:10pt;"><FONT size="2"><SPAN style="font-family: Arial,sans-serif;">Hi people, i have a problem with inner join and temporary tables<BR><BR>I have 2 tables: articles and existences<BR><BR>articles <BR>CREATE TABLE public.articles<BR>(<BR> art_cod character varying(5) NOT NULL DEFAULT ''::character varying,<BR> art_descri character varying(20) DEFAULT ''::character varying,<BR> CONSTRAINT articles_pkey PRIMARY KEY (art_cod)<BR>) <BR><BR>"1";"nails"<BR>"2";"hammers"<BR>"3";"wood"<BR><BR>existences <BR>CREATE TABLE public.existences<BR>(<BR> art_cod character varying(5) DEFAULT ''::character varying,<BR> exis_ubic character varying(20) DEFAULT ''::character varying,<BR> exis_qty numeric(8) DEFAULT 0<BR>) <BR><BR>"1";"new york";100<BR>"1";"dallas";130<BR>"2";"miami";1390<BR>"3";"baltimore";390<BR>"3";"louisiana";20<BR><BR>And a function that is due to relate both tables and give me a list of articles with<BR>ubication and quantity.<BR>Whati do in the function is first load 2 temporary tables, then the inner join.<BR>I know this is no the best way, but i would like to know why it does not work. Notice that <BR>in ms sql server it works fine.<BR><BR><BR>CREATE OR REPLACE FUNCTION public.test1 (out art_cod varchar,out art_descri varchar, <BR>out exis_ubic varchar, out exis_qty numeric) returns setof record as<BR>$body$<BR><BR>select * into temp table t_arti from public.articles;<BR>select * into temp table t_exis from public.existences;<BR><BR>select a.art_cod,a.art_descri,e.exis_ubic,e.exis_qty<BR>from t_arti a inner join t_exis e on a.art_cod= e.art_cod;<BR>$body$<BR> LANGUAGE 'sql' VOLATILE;<BR><BR><BR><BR>When i call the function with this line:<BR><BR>select * from modelo.test1()<BR><BR><BR>This message appears:<BR><BR>ERROR: relation "t_arti" does not exist<BR>SQL state: 42P01<BR>Context: SQL function "test1"<BR><BR><BR>Why it does not work???<BR>thanks for your help</SPAN></FONT><BR> <BR><HR>Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.com<BR>Join Linux Discussions! --> http://Community.LinuxWaves.com</DIV>
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.
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 helpBecause 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.
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
Import Notes
Resolved by subject fallback
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