Dynamic query in function

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.
xlprofAsked:
Who is Participating?
 
adrpoConnect With a Mentor Commented:

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
 
xlprofAuthor Commented:
Ok thanks adrpo, your solution works....we need this for a more complex functions. Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.