Search Postgresql Archives

inner join problem with temporary tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi people, i have a problem with inner join and temporary tables

I have 2 tables: articles and existences

articles
CREATE TABLE public.articles
(
  art_cod character varying(5) NOT NULL DEFAULT ''::character varying,
  art_descri character varying(20) DEFAULT ''::character varying,
  CONSTRAINT articles_pkey PRIMARY KEY (art_cod)
)

"1";"nails"
"2";"hammers"
"3";"wood"

existences
CREATE TABLE public.existences
(
  art_cod character varying(5) DEFAULT ''::character varying,
  exis_ubic character varying(20) DEFAULT ''::character varying,
  exis_qty numeric(8) DEFAULT 0
)

"1";"new york";100
"1";"dallas";130
"2";"miami";1390
"3";"baltimore";390
"3";"louisiana";20

And a function that is due to relate both tables and give me a list of articles with
ubication and quantity.
Whati do in the function is first load 2 temporary tables, then the inner join.
I know this is no the best way, but i would like to know why it does not work. Notice that
in ms sql server it works fine.


CREATE OR REPLACE FUNCTION public.test1 (out art_cod varchar,out art_descri varchar,
out exis_ubic varchar, out exis_qty numeric) returns setof record as
$body$

select * into temp table t_arti from public.articles;
select * into temp table t_exis from public.existences;

select a.art_cod,a.art_descri,e.exis_ubic,e.exis_qty
from t_arti a inner join t_exis e on a.art_cod= e.art_cod;
$body$
 LANGUAGE 'sql' VOLATILE;



When i call the function with this line:

select * from modelo.test1()


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

 

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux