Issues with PL/pgSQL code

Question

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

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.
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.

The name of the procedural language is [`PL/pgSQL` or simply `plpgsql`](http://www.postgresql.org/docs/current/interactive/plpgsql.html). And the name of the RDBMS is [`PostgreSQL` or `Postgres` for short](http://wiki.postgresql.org/wiki/Identity_Guidelines). At least that you could have found out yourself. Please present a halfway decent question, and don't be afraid of [formatting aids](http://stackoverflow.com/editing-help). — Erwin Brandstetter, Jul 08 '14 at 22:32
Thank you for your inputs. I would try to put a decent question next time onwards. — Kausty, Jul 09 '14 at 00:00
Aside: Do not quote the language name in `LANGUAGE plpgsql`. It's an identifier, not a string. Tolerated for now, but may go away in future releases. — Erwin Brandstetter, Jul 09 '14 at 05:04

score 4 · Accepted Answer · edited May 23 '17 at 11:46

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: \_.

score 3 · Answer 2 · answered Jul 08 '14 at 21:55

You can avoid creating a permanent function by using a DO $$ $$ script: http://www.postgresql.org/docs/9.3/static/sql-do.html

However, I'm not clear on how you'd avoid having PL/pgSQL code entirely, given that you're defining a cursor based on the contents of another query. You're using PL/SQL code in Oracle; why wouldn't you need procedural code in PostgreSQL?

You can certainly use OPEN CURSOR FOR EXECUTE in your PL/pgSQL function if you have a preference for cursors, but you can't use the same structure you have in Oracle; in PL/pgSQL, you can't DECLARE a cursor as a string substitution. That's only done during OPEN: http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html

Aside from the thought that having an EAV settings table for each user isn't a great design ...

Issues with PL/pgSQL code

2 Answers2

Major points