Обсуждение: [SQL] Using Array in Function
Hello,
I came across something strange about function. I have a function that has an input parameter is an array like integer[] and return json value. This input parameter is used in where clause.
When I call function I'm using input parameter like '{int1,int2,int3}' then return only result for first one int1. If I only execute sql script that is in function with same parameter in where clause, sql script return 3 rows that we need.
It seems so weird. Both of results should be same for function and sql script of function.
What do you think about that? If you need I can share base of function structure.
Regards,
I came across something strange about function. I have a function that has an input parameter is an array like integer[] and return json value. This input parameter is used in where clause.When I call function I'm using input parameter like '{int1,int2,int3}' then return only result for first one int1. If I only execute sql script that is in function with same parameter in where clause, sql script return 3 rows that we need.It seems so weird. Both of results should be same for function and sql script of function.
A function will only return more than one row if it is defined as either:
RETURNS TABLE
or
RETURNS SETOF <type>
A script will always return all available rows.
David J.
Hi David,
Thank you for quick response. Your advice solved my problem :)
Regards,
Gunce
On Mon, Oct 9, 2017 at 11:12 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I came across something strange about function. I have a function that has an input parameter is an array like integer[] and return json value. This input parameter is used in where clause.When I call function I'm using input parameter like '{int1,int2,int3}' then return only result for first one int1. If I only execute sql script that is in function with same parameter in where clause, sql script return 3 rows that we need.It seems so weird. Both of results should be same for function and sql script of function.A function will only return more than one row if it is defined as either:RETURNS TABLEorRETURNS SETOF <type>A script will always return all available rows.David J.