Dynamic SQL in Postgres

Question

I've coded a simple Function using Postgres but keep getting the following:

ERROR: syntax error at or near "$2".

The underlying database is ParAccel and I'm new to both Postgres and ParAccel. I'm using TOAD Data Point as the IDE:

CREATE OR REPLACE FUNCTION GET_NEXT_SURR_KEY(I_SCHEMA_NM VARCHAR, I_TABLE_NM VARCHAR,I_COLUMN_NM VARCHAR,I_POSNEG_FLAG VARCHAR) 
RETURNS BIGINT
LANGUAGE PLPGSQL
AS $body$
DECLARE
    O_RET_VALUE BIGINT := 0;
    V_DYN_SQL VARCHAR(2000) := '';
BEGIN
    IF I_POSNEG_FLAG = 'P' THEN
       V_DYN_SQL := 'SELECT MAX(' || I_COLUMN_NM || ') + 1 FROM ' || I_SCHEMA_NM || '.' || I_TABLE_NM;

    ELSE
       V_DYN_SQL := 'SELECT MIN(' || I_COLUMN_NM || ') - 1 FROM ' || I_SCHEMA_NM || '.' || I_TABLE_NM;
    END IF;

    EXECUTE V_DYN_SQL INTO O_RET_VALUE;

    RETURN O_RET_VALUE;
END $body$

I'm using the following example command to execute the Function:

{CALL GET_NEXT_SURR_KEY('some_schema_name','some_table_name','some_column_name','P')};

Can anyone please let me know where I'm messing up?

Thanks in advance.

Why aren't you using sequences? Using `select max()` to get a unique ID doesn't work and is dead-slow as well. — , Apr 04 '14 at 14:51
+1 to the above. This is totally wrong design. See http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id/2944481#2944481 — leonbloy, Apr 04 '14 at 15:04
Not using Sequencer on ParAccel is a design decision at the shop and not within my control and is being done currently on DB2. Select Max() works fine if I use hard coded column name and table name. Response time is not an issue as the volume level is low. So getting back to my original question.... — user3498592, Apr 04 '14 at 15:10
Ugh, don'y use `||` when constructing dynamic SQL. Use the `format` function with the `%I` format-specifier. It'll do the quoting correctly for you. Or at least use `quote_ident`. — Craig Ringer, Apr 04 '14 at 15:25
Thanks for the link leanbloy, I'm going to quote from that link: "One last general note: none of this methods works if you intend to get the last globally inserted id (not necessarily in your session). For this, you must resort to select max(id) from table (of course, this will not read uncommitted inserts from other transactions)." — user3498592, Apr 04 '14 at 15:26
`select max()` does ***NOT*** "work fine". That concept is plain broken in an environment with more than one concurrent connection to the database. The *very* least you need to do is to *exclusively* **lock** the table before running the select. — , Apr 04 '14 at 15:41
This is a datawarehouse environment! Please concentrate on the question on hand rather than critiquing the design. — user3498592, Apr 04 '14 at 16:07

kristok · Answer 1 · 2014-04-11T23:10:03.053

0

Trust the horse, use sequences - because you are in an OLAP environment you most likely will not get uniqueness violations but if this would be a normal website you would get the same id twice very often. As for your function it works perfectly well - tested it on a random table in my database and no error was given so look for the fault in TOAD.

edited Apr 11 '14 at 23:10

answered Apr 11 '14 at 22:58

kristok

152
1
6

Thanks for testing out the code. No, its not TOAD. I created the same Function using DBVisualizer and I still get the same error. It has something to do with ParAccel and PostgreSQL, they don't seem to talk well. Unfortunately, there are no native ParAccel drivers (for now) so will have to muddle through PostgreSQL for the time being to access ParAccel – user3498592 Apr 15 '14 at 14:47

score 0 · Accepted Answer · answered May 13 '14 at 12:32

ParAccel has the concept of IDENTITY fields, not sure why you are not using them.

But in any case, here is how to solve your problem.

BTW, I believe the code you wrote would work on PostgreSQL 9 or above, but ParAccel is using version 7.02 (If I'm not mistaken) which doesn't support SELECT INTO a variable so you need to capture the result with a record and extract the value using a loop (I didn't re-wrote all your function, just the main part)

CREATE OR REPLACE FUNCTION GET_NEXT_SURR_KEY(I_SCHEMA_NM VARCHAR, I_TABLE_NM VARCHAR,I_COLUMN_NM VARCHAR,I_POSNEG_FLAG VARCHAR) 
RETURNS BIGINT
LANGUAGE PLPGSQL
AS $body$
DECLARE
    O_RET_VALUE BIGINT default 0;
    V_DYN_SQL VARCHAR(2000) := '';
    _ret_rec record; 
BEGIN


     V_DYN_SQL := 'SELECT MAX(' || I_COLUMN_NM || ') + 1 as new_id FROM ' || I_SCHEMA_NM || '.' || I_TABLE_NM;

    FOR _ret_rec IN EXECUTE V_DYN_SQL 
    LOOP 
        O_RET_VALUE := _ret_rec.new_id; 
    END LOOP; 


    RETURN O_RET_VALUE;
END $body$

Thank you Diego!!! That worked!!! I highly appreciate your feedback... As to why we are not using Identity column is because we have to do a migration for around 500 jobs from DB2 to ParAccel and want to minimize the changes for the migration. Thanks again — user3498592, May 14 '14 at 13:27

Dynamic SQL in Postgres

2 Answers2