Re: function that works only once...

On Thu, 31 Jul 2003, Niclas Hedell wrote:

> Hi list,
>
> I've a strange problem here that someone might know the answer to. I
> have a function in plpgsql that works only the first time after
> creating (installing) the function. After that I get the following
> error:
> Fatal - ERROR: pg_class_aclcheck: relation 51755 not found
>
> I've tried to find pg_class_aclcheck in pg_class but it's not there.
> Here is my function:
>
> create or replace function display_attributes(varchar) returns varchar as '
> declare
> classNameVar alias for $1;
> query_string text;
> tablename varchar(20);
> begin
> query_string := ''create view the_groupview as select * from
> getattribute('' || quote_literal(classNameVar) || '')'';
> execute query_string;
> select into tablename the_tablename from the_groupview;
> drop view the_groupview;
> return tablename;
> end;'
> language 'plpgsql' with (isstrict);

All queries that relate to the_groupview need to be done via
execute, otherwise the query plans will be saved with for the
view that's been dropped (someday such plans will get redone
when necessary).

Maybe something like (untried):
create or replace function display_attributes(varchar) returns varchar as
'
declare
classNameVar alias for $1;
query_string text;
tablename varchar(20);
r record;
begin
query_string := ''create view the_groupview as select * from
getattribute('' || quote_literal(classNameVar) || '')'';
execute query_string;
for r in execute ''select the_tablename from the_groupview'' loop
tablename := r.the_tablename;
end loop;
execute ''drop view the_groupview'';
return tablename;
end;'
language 'plpgsql' with (isstrict);