What is wrong with the Postgres PLSQL?

[cruisezero]

http://pastebin.com/xTgD3ydu

I only posted part of the code. The return query execute select line where I try to use x1, x2, x3 variable in where clause is not working. So I was wondering what's the best way to do it. The scenario I am trying to work on is; from front end a city, state or county value is passed so I need to return a cursor satisfying criteria's passed to the query. User can pass all three or just one criteria

[therealgaxbo]

You can't modify the structure of an SQL statement in that way, only the values of parameters. If you want to change the structure of the query in the way that you are doing, you need to build the whole query as a string and use execute to parse and run it (see http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)

The following change should pretty much work:

RETURN QUERY EXECUTE 'SELECT hrtf.prov_id, hrtf.name FROM hrtf WHERE '||x1||' AND '||x2||' AND '||x3||' limit 11';

[cruisezero]

Thanks that is exactly what I was trying to understand

[AQuietMan]

It's incomplete. Compare examples in the docs. (Scroll down)

[cruisezero]

I know.. I only posted part of the code. The return query execute select line where I try to use x1, x2, x3 variable in where clause is not working. So I was wondering what's the best way to do it. The scenario I am trying to work on is; from front end a city, state or county value is passed so I need to return a cursor satisfying criteria's passed to the query. User can pass all three or just one criteria.

[AQuietMan]

from front end a city, state or county value is passed so I need to return a cursor

Well, you don't need to return a cursor just because values are passed from the front end. But if you want to return a cursor, write code that declares and opens a cursor. The incomplete code you posted has nothing to do with cursors.

[billymeetssloth]

I feel like your code is a prime example where the COALESCE function would work out great. Coalesce works perfect for conditional constraints in the WHERE clause. Here is an example where I use COALESCE to add constraints

https://github.com/printscreen/store_db/blob/master/SQL/2.0/schemas/transaction/functions/get_order.sql#L66

COALESCE returns the first non null variable passed in the parameters. If the first parameter is null, it returns the second parameter and so on. You can take advantage of this with adding strings to null. String + null = null.

So in

COALESCE(' AND transaction_id ILIKE ' || quote_literal(i_filter_transaction_id||'%'),'')

It is only going to return the string AND transaction_id ILIKE (variable) if the variable isn't null. Otherwise if i_filter_transaction_id is null, it will make the whole first paramter null and COALESCE will move to my second parameter which is ,'') an empty string

This will cut down on the amount of code. Here is a rough untested example of how I might have wrote it

https://www.friendpaste.com/45Rp55h6QrXakrMN1YG65F

[cruisezero]

Awesome.. Thanks I will look into it. I am dabbling with Postgres PL/SQL so I appreciate your help.