If I had hair, I would have lost it by now.
I'm trying to create a function that passes a string that will need to be
quoted. I finally figured out how many single quotes I needed and as a
test, I've shown it in v_sql. However, I now need to do the same thing for
my FOR...LOOP query.
The end result should be something like this:
Select * from pg_views where definition ~* 'product_non_master_v'
I've tried numerous variations of quotes, but my return value is always
null. If I output the v_sql variable, it gives me what I need to run a
successful query.
Any help would be greatly appreciated:
CREATE or REPLACE FUNCTION recompileview(varchar) RETURNS varchar AS '
DECLARE
tblname ALIAS FOR $1;
old_view RECORD;
v_sql varchar;
begin
/* This is what I need */
v_sql := ''Select * from pg_views where definition ~* '''''' || tblname ||
'''''''' ;
FOR old_view in Select * from pg_views where definition ~*
''''tblname'''''' LOOP
v_name := v_name || old_view.viewname;
END LOOP;
RETURN v_name;
end;
' LANGUAGE 'plpgsql';
TIA
Patrick Hatcher