<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>