Ordinarily, when you write select statements (for example) in a plpgsql function, it will attempt to cache the execution plan. In your case, though, you want to be able to hit different tables each time your function is invoked, so you need a way to construct and execute your query dynamically. That's where the EXECUTE statement comes in. EXECUTE allows you to issue a command that is prepared every time it is run.
In your case, though, things are a bit trickier. There's no way to get the results of a dynamically executed select statement within a plpgsql function (according to the docs, the results are discarded). In your example, you need to be able to run a dynamic sql statement and get a result back.
I thought a temp table might work in this situation, so I tried something like this (using PostgreSQL 8.0 beta 4):
create or replace function count_rows(table_name text) returns integer as
$$
declare c integer;
begin
execute 'select count(*) into temp count_tbl from ' || quote_ident(table_name);
select count into c from count_tbl;
return c;
end;
$$
language 'plpgsql';
Unfortunately, you can't use EXECUTE to do a SELECT INTO. So, as if that wasn't ugly enough, I ended up having to do the following:
create or replace function count_rows(table_name text) returns integer as
$$
declare c integer;
begin
execute 'create temp table count_tbl(count integer)';
execute 'insert into count_tbl(count) select count(*) from ' || quote_ident(table_name);
select count into c from count_tbl;
return c;
end;
$$
language 'plpgsql';
That works, but it is definitely not very pretty (if you use it, you'll probably want to also add some code to drop the temp table...if you search through the recent messages on this list, there's question I asked about adding such a cleanup mechanism to a function that may be helpful).
If anyone knows a cleaner way to solve Alexander's problem, I'd be really interested to hear it!
Hope this helps, Tim
On Nov 23, 2004, at 5:32 PM, Alexander Pucher wrote:
Tim,
I'm afraid, I didn't get the point. Could you give me an example code snippet of how to use the EXECUTE command in my case. Do I have to use the EXECUTE within my function?
Thanks a lot, alex.
Timothy Perrigo wrote:
You'll need to use the EXECUTE command to build the SQL dynamically.
See:
http://www.postgresql.org/docs/7.4/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
On Nov 23, 2004, at 2:56 PM, Alexander Pucher wrote:
Hi,
struggling around with this for some time:
How can I use a table name as a parameter in a PL/pgSQL function ??
I tried this but it didn't work...
CREATE OR REPLACE FUNCTION my_row_count(text) RETURNS int4 AS ' DECLARE num_rows int4; BEGIN num_rows := (select count(*) from $1); RETURN num_rows; END; ' LANGUAGE plpgsql;
Thnaks for any input!
regards, alex.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
-------------------------------------------------------- Departement of Geography and Regional Research
University of Vienna
Cartography and GIS
--------------------------------------------------------
Virtual Map Forum: http://www.gis.univie.ac.at/vmf
--------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
