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