asked on

RETURN table columns issue

Hi,

we are getting syntax error when we have a return table column matches with any other table columns in the function. If we need to use of one of return table column data and insert into another table which has same column name, it seems, it is not allowed, any work around on this? please find below code.

CREATE OR REPLACE FUNCTION public.get_country9() 
 RETURNS TABLE (
  [b]country_id[/b] integer,
  country character varying(50),
  last_update timestamp without time zone
  )
AS $BODY$

BEGIN
 RETURN QUERY 
 SELECT
 c.country_id,
 c.country,
 c.last_update
 FROM
 public.country c;
  
insert into public.test_insert
(  city,
  [b]country_id[/b]) 
 select 'LONDON',222;

 insert into public.test_insert
(  city,
  [b]country_id[/b]) 
 select 'TORONTO',111;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

Open in new window

ERROR:

ERROR: 42601: syntax error at or near "$1"

Open in new window

ste5an

Functions must be side-effect free. You cannot insert data in real table..

Did you mean something like this:

CREATE OR REPLACE FUNCTION public.get_country9()
 RETURNS TABLE (
  country_id integer,
  country character varying(50),
  last_update timestamp without time zone
  )
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT  c.country_id,
            c.country,
            c.last_update
    FROM    public.country c
    UNION
    SELECT  222,
            'LONDON',
            NULL
    UNION
    SELECT  111,
            'TORONTO',
            NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

Open in new window

ASKER

Thanks for the reply. Our requirement is along with returning return query output through table return also need to log part of the information in the log table which has some of column names same as return table columns. please find below test code for the scenario. please advise if you have any work around solution.

drop function if exists public.get_country9(integer,character varying);

CREATE OR REPLACE FUNCTION public.get_country9(integer,character varying) 
 RETURNS TABLE (
  [b]country_id[/b] integer,
  country character varying(50),
  last_update timestamp without time zone
  )
AS $BODY$

DECLARE
v_country_id ALIAS FOR $1;
v_country ALIAS FOR $2;

BEGIN
    
insert into public.test_log
(  [b]country_id[/b],
  country) 
 select 
 v_country_id,
 v_country;
 
 RETURN QUERY 
 SELECT
 c.[b]country_id[/b],
 c.country,
 c.last_update
 FROM
 public.country c 
 where c.country_id= v_country_id 
 or c.country=v_country;
   
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

Open in new window

ste5an

hmm, I don't have a test instance. But you either use a stored procedure or you look into the audit capabilities of Postgres (e.g. pgAudit).

ASKER

we are using Postgres 8.23 on Greenplum, PgAudit is not available.