Thread: syntax error on Function return setoff

Hi All,

                I have a working function that returns setoff record was created under porstgresql 9.6 but  when I try to create the same function under release 10 it gives me error type”recode” does not exist.  Therefore, I changed it to RETURNS SETOF table_name.column%varachar(7) I got syntax error.  Can anyone let me know the right syntax?

On 26 January 2018 19:39:27 CET, Garry Chen <gc92@cornell.edu> wrote:
>Hi All,
>I have a working function that returns setoff record was created under
>porstgresql 9.6 but  when I try to create the same function under
>release 10 it gives me error type”recode” does not exist.  Therefore, I
>changed it to RETURNS SETOF table_name.column%varachar(7) I got syntax
>error.  Can anyone let me know the right syntax?
>
>Thank you very much,
>Garry


Maybe a typo? "recode" is wrong...

Can you show the function-definition?


Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Here is the short/simple function in postgresql 9.6.

CREATE OR REPLACE FUNCTION public.sec_select_labor_data(
    )
    RETURNS SETOF acct_nbr_lst 
    LANGUAGE 'plpgsql'

AS $BODY$
Declare
     v_cnt        numeric;
     sec_role     varchar(20);
     v_netid      varchar(30);
begin

    RETURN QUERY Select acct_nbr from kdw_acct_security where lower(netid) = CURRENT_USER;

end;

$BODY$;

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

Hi Andreas,
    Thank you very much for your reply.  Very strange, in 9.6 the function created/compiled successful  without the
tablecalled "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
Postgresqldocuments 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@a-kretschmer.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