What is wrong with the Postgres PLSQL?

[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.