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