CODEWITHSUNDEEP
postgresqlparaccel
user3498592
user3498592

Reputation: 33

Dynamic SQL in Postgres

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.

Upvotes: 2

Views: 509

Answers (2)

Diego
Diego

Reputation: 36166

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$

Upvotes: 0

kristok
kristok

Reputation: 150

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.

Upvotes: 0

Related Questions