Issues with PL/pgSQL code

Question

I am not very much experienced in plpgsql so I am having 2 questions here.

1. Is it possible in Postgres to use cursors the way we use in dynamic sql's for plsql? Basically I did not want to use procedure in plsql so I created a cursor which will hold the output of my dynamic query and then I use that value in a loop inside another query to get my output displayed on the screen. The same thing I am trying to with Postgres but not able to do so.
2. Is it possible to in such situation to avoid creating a permanent function to accomplish this?

Here is my oracle script which runs fine:

DECLARE
  CURSOR cur_tables IS
     SELECT  NAME,
'SELECT PROPERTY_VALUE FROM '
         || USERNAME
         || '.P_PROPERTY WHERE PROPERTY_NAME = ''VERSION'''
                 AS dsql
       FROM CB_DATASOURCE
      WHERE (UPPER(USERNAME) LIKE 'NAV_PS_%' or UPPER(USERNAME) LIKE 'CBPS_%' or UPPER(USERNAME) LIKE 'DEFAULTPS');
  CURR_VERSION VARCHAR2(1000);
BEGIN
  FOR r_tables IN cur_tables LOOP
   begin
     EXECUTE IMMEDIATE r_tables.dsql INTO CURR_VERSION;
     DBMS_OUTPUT.put_line(r_tables.NAME || ': ' || CURR_VERSION);
    exception
     when others then
      DBMS_OUTPUT.put_line(r_tables.NAME || ' no table');
   end;   
  END LOOP;
END;
/

and here is my postgres function which I am not able to get it working but eventually would like to refrain from using a permanent function

create or replace function upgrade_version() returns setof record as $$ 
declare
 r record;
 loopy record;
 isql text;
 CURR_VERSION text;
begin
 for r in SELECT 'SELECT PROPERTY_VALUE FROM '
         || USERNAME
         || '.P_PROPERTY WHERE PROPERTY_NAME = ''VERSION'''
                 AS dsql
       FROM CB_DATASOURCE
      WHERE (UPPER(USERNAME) LIKE 'NAV_PS_%' or UPPER(USERNAME) LIKE 'CBPS_%' or UPPER(USERNAME) LIKE 'DEFAULTPS') loop
  isql := r.dsql;
  EXECUTE isql INTO CURR_VERSION;
  RETURN next loopy;
 end loop;
 return;
end;
$$ language 'plpgsql';

I would really appreciate any inputs on this.

Answer 1

What you seem to be trying should work with a DO statement:

DO
$do$ 
DECLARE
   r record;
   curr_version text;
BEGIN
   FOR r IN 
      SELECT name, format($$
         SELECT property_value
         FROM   %I.p_property
         WHERE  property_name = 'VERSION'$$, username) AS dsql
      FROM   cb_datasource
      WHERE  upper(username) LIKE ANY ('{NAV_PS_%, CBPS_%, DEFAULTPS}')
   LOOP
      BEGIN
         EXECUTE r.dsql INTO curr_version;
         RAISE NOTICE '%: %', r.name, curr_version;
      EXCEPTION WHEN OTHERS THEN
         RAISE NOTICE '%: no table', r.name;
      END;   
   END LOOP;
END
$do$

Major points

• You cannot RETURN from a DO statement, but you can RAISE notices et al.
  Seems to be perfect for you since you eventually would like to refrain from using a permanent function.
  Note that the default procedural language for DO statements is still plpgsql.

• In Postgres, unquoted identifiers are cast to lower case (as opposed to Oracle, where they are upper-cased).

• To catch exceptions in a loop you need to wrap the body in a separate block. Read the manual here.

• You need to sanitize identifiers in dynamically built SQL strings lest you are vulnerable to SQL injection and other problems from non-standard names. Your Oracle code is lacking there as well.
  I am using format() with %I for the purpose. More info:
  How to use EXECUTE FORMAT ... USING in postgres function

• Shortened your WHERE clause with ANY

• You are aware that _ is a placeholder character in LIKE patterns? For literal _, escape it: \_.