Thread: Insert Table from Execute String Query
I need to insert temp table from execute string query. How can I do ? I'm trying like that but not working.
CREATE OR REPLACE FUNCTION public.testdyn
(
x integer
)
RETURNS TABLE
(
x integer
)
RETURNS TABLE
(
id bigint,
text character varying(4000)
)
AS $$
DECLARE mysql TEXT;
BEGIN
create temp table tmp1
(
id1 bigint,
text character varying(4000)
);
mysql = 'select id, text from TEST';
id bigint,
text character varying(4000)
)
AS $$
DECLARE mysql TEXT;
BEGIN
create temp table tmp1
(
id1 bigint,
text character varying(4000)
);
mysql = 'select id, text from TEST';
RETURN QUERY
EXECUTE mysql INTO tmp1 ;
END;
$$ LANGUAGE plpgsql;
EXECUTE mysql INTO tmp1 ;
END;
$$ LANGUAGE plpgsql;
On 12/6/19 1:00 AM, İlyas Derse wrote: > I need to insert temp table from execute string query. How can I do ? > I'm trying like that but not working. > > CREATE OR REPLACE FUNCTION public.testdyn > ( > x integer > ) > RETURNS TABLE > ( > id bigint, > text character varying(4000) > ) > AS $$ > DECLARE mysql TEXT; > BEGIN > create temp table tmp1 > ( > id1 bigint, > text character varying(4000) > ); > mysql = 'select id, text from TEST'; > > RETURN QUERY > EXECUTE mysql INTO tmp1 ; > END; > $$ LANGUAGE plpgsql; It would help to know what you are trying to do. In the meantime: 1) create table test (id bigint, text varchar); insert into test values (1, 'test1'), (2, 'test2'); 2) Running your function: select testdyn(1); ERROR: query "SELECT mysql INTO tmp1" is not a SELECT CONTEXT: PL/pgSQL function testdyn(integer) line 11 at RETURN QUERY 3) Change to function: CREATE OR REPLACE FUNCTION public.testdyn(x integer) RETURNS TABLE(id bigint, text character varying) LANGUAGE plpgsql AS $function$ DECLARE mysql TEXT; BEGIN create temp table tmp1 ( id1 bigint, text character varying(4000) ); mysql = 'select id, text from TEST'; drop table tmp1; RETURN QUERY EXECUTE mysql; END; $function$ select * from testdyn(1); id | text ----+------- 1 | test1 2 | test2 -- Adrian Klaver adrian.klaver@aklaver.com