Re: syntax error on Function return setoff

Hi Andreas,
Thank you very much for your reply. Very strange, in 9.6 the function created/compiled successful without the table called "acct_nbr_lst". But in release 10 this table "acct_nbr_lst" must exist in order to create/compiled this function. In RDBMS function/procedure coding principal, I don’t know which one is the correct way. Is there any Postgresql documents that states/mentation the prerequisite about the SETOF? Once again thank you very much for your help.

Garry

On 1/27/18, 5:35 AM, "Andreas Kretschmer" <andreas(at)a-kretschmer(dot)de> wrote:

Am 26.01.2018 um 20:01 schrieb Garry Chen:
> Here is the short/simple function in postgresql 9.6.
>
> CREATE OR REPLACE FUNCTION public.sec_select_labor_data(

works for me, in 9.6 and 10. first i create a new table (and impliciet
the typ acct_nbr_lst)

test=# create table acct_nbr_lst (i int);
CREATE TABLE
test=*# CREATE OR REPLACE FUNCTION public.sec_select_labor_data(
test(# )
test-# RETURNS SETOF acct_nbr_lst
test-# LANGUAGE 'plpgsql'
test-#
test-# AS $BODY$
test$# Declare
test$# v_cnt numeric;
test$# sec_role varchar(20);
test$# v_netid varchar(30);
test$# begin
test$#
test$# RETURN QUERY Select acct_nbr from kdw_acct_security where
lower(netid) = CURRENT_USER;
test$#
test$# end;
test$#
test$# $BODY$;
CREATE FUNCTION
test=*#


works in 9.6 and 10.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com