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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
