syntax error on Function return setoff

[Garry Chen]

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

[David G. Johnston]

Post by Garry Chen
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?
AFAIK there is nothing changed between 9.6 and 10 that should affect

this. Providing the complete create function statement (you can probably
replace the body string with raise notice or something similar) would be
helpful, but "recode" is an odd, and custom, type name...

As for the spec using "%", the point of it is to infer the data type from
the named column, which means you shouldn't actually have a data type name
(i.e., varchar) in the expression. The text TYPE in the docs are the
literal characters TYPE, not a syntax placeholder. The difference is the
"table.column" are in italics while "%TYPE" is not.

David J.

[Andreas Kretschmer]

Post by Garry Chen
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

[Garry Chen]

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;

[Andreas Kretschmer]

Post by 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

[Garry Chen]

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

Post by 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 Suppor

[David G. Johnston]

Post by Garry Chen
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.

​Anything outside of the string-literal function body (which includes the
RETURNS clause) has to exist and is recorded as a dependency. The material
within a function body usually (not sure if/when expections...) is not
required to exist and is not recorded as a dependency.​

David J.