How building a dynamic query in a postgresql-function?

Posted on 2005-05-16
Medium Priority
Last Modified: 2009-12-16

I would like to know how I've to build and execute a dynamic sql query in a postgresql-function.

More details :

I've 3 parameters :
  * Customer Name
  * Customer First Name
  * Customer Account Number

some of those fields are filled in. The postgre-sql function receive the 3 parameters. In the function I want to create a select query with the filled-in values (not blank values).

I've noticed that the execute functions doesn't return any results.

Can you please send me a short example?

Thanks a lot!

Question by:davyberroho
LVL 22

Accepted Solution

earth man2 earned 2000 total points
ID: 14014754
In what context are you doing this -- the only rational context is pl/pgsql
I which case you would use an explicit cursor viz

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

or implicit cursor viz

FOR record_or_row IN EXECUTE text_expression LOOP


Author Comment

ID: 14055889
thanks, exactly what I needed.

works great !

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question