Обсуждение: Table name as parameter in function
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.
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 > majordomo@postgresql.org >
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 >> majordomo@postgresql.org >> > > > ---------------------------(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 --------------------------------------------------------
Sorry for the brief response earlier; I was a bit rushed. After
looking into it, it's a bit messier than I thought (at least, as far as
I can tell...perhaps one of the gurus on this list can show us a better
way).
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
>>> majordomo@postgresql.org
>>>
>>
>>
>> ---------------------------(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
>
On Tue, 23 Nov 2004, Timothy Perrigo wrote: > Sorry for the brief response earlier; I was a bit rushed. After > looking into it, it's a bit messier than I thought (at least, as far as > I can tell...perhaps one of the gurus on this list can show us a better > way). > > 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 Explain as a statement doesn't return results, but FOR recordvar IN EXECUTE ... should work. It's still ugly, but something like create or replace function count_rows(table_name text) returns integer as $$ declare foo record; begin for foo in execute 'select count(*) as count from ' || quote_ident($1) loop return foo.count; end loop; end; $$ language 'plpgsql'; should work for 8.0b. IIRC, at least 7.4 should work similarly if you change the quoting.