Re: Insert Table from Execute String Query

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