[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic query in function

Posted on 2007-10-19
2
Medium Priority
?
244 Views
Last Modified: 2012-06-21
I want to create a function that returs the number of records from a postgres database such as the following. The  sql is dynamic as the table is created as the table is a partition.

CREATE OR REPLACE FUNCTION "public"."function1" (TEXT) RETURNS INTEGER AS
$body$
DECLARE
vText ALIAS FOR $1;
vSQL text;
  vResult  integer;
  BEGIN
     vSQL:= 'select  count(*)  from table'||$1;
     vresult:= EXECUTE vSQL;
   
   RETURN vResult;
 END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

What's wrong with my function.
0
Comment
Question by:xlprof
2 Comments
 
LVL 10

Accepted Solution

by:
adrpo earned 500 total points
ID: 20113826

Hi,

You used vText as alias for $1 but then you use directly $1
in vSQL := 'select  count(*)  from table'||$1;
You use different cases for vResult vs. vresult

I would use:
vSQL := 'select count(*) from table'||vText;
EXECUTE vSQL into vResult;
return vResult;

Cheers,
za-k/
0
 

Author Comment

by:xlprof
ID: 20113989
Ok thanks adrpo, your solution works....we need this for a more complex functions. Thanks a lot
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

865 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