Postgresql function invocation

Question

I've written a postgresql function shown as follows

CREATE OR REPLACE FUNCTION public."UpdateTx"(IN "instructionId" character varying,IN txdata character varying,IN txdetail character varying,IN txstatus character varying,IN resid character varying,IN "timestamp" bigint)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100
    
AS $BODY$DECLARE updateClause varchar;
BEGIN

IF instructionId = '' THEN
    RAISE EXCEPTION 'instruction id is missing';
END IF;
IF txstatus = '' THEN
    RAISE EXCEPTION 'tx status is missing';
END IF;
updateClause := CONCAT('txstatus= ', txstatus);
IF txData != '' THEN
    updateClause = CONCAT(updateClause, ', ', 'txdata=', txdata);
END IF;
EXECUTE 'UPDATE transactions SET $1 WHERE instructionid=instructionid' USING updateClause;

END;
$BODY$;

So it expects 5 varchar & 1 bigint as input arguments. I've tried to execute the following SQL query

Select UpdateTx('123'::varchar, 'test'::varchar, 'test'::varchar, 'test'::varchar, 'test2'::varchar, 4124::bigint)

but it keeps showing this error message

ERROR:  function updatetx(character varying, character varying, character varying, character varying, character varying, bigint) does not exist
LINE 1: Select UpdateTx('123'::varchar, 'test'::varchar, 'test'::var...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

Is the syntax incorrect?

Appreciate any suggestions or answers :)

Unquoted identifiers are folded to lower-case. You used quotes in your CREATE OR REPLACE, so the function name is a case-sensitive "UpdateTx", but then are using unquoted UpdateTx in your SELECT, which gets converted to updatetx, which can't be found. — AdamKG
– AdamKG, Commented Jul 20, 2020 at 13:53
Why the dynamic SQL? As you don't have dynamic identifiers (table, column names) there is absolutely no need for that. — user330315
– user330315, Commented Jul 20, 2020 at 14:04

GMB · Accepted Answer · 2020-07-20 14:06:30Z

1

Your immediate problem is that your function is declared as case-sensitive (with surrounding double quotes), but you call it in a case-insensitive manner (without the quotes, which, to Postgres, is equivalent to all lower caps). The names just do not match.

But there is more to it:

the way you pass variables is not OK; instead of concatenating part of the query into variable updateClause, you should pass a parameter for each value that needs to be passed to the variable - but better yet, you don't actually need dynamic SQL for this
Don't use variables that conflict with column names
I suspect that you want nulls instead of empty string (it makes much more sense to indicate the "lack" of a value)
the function needs to return something
I also notice that you are not using all arguments that are passed to function

Here is a function code, that, at least, compiles. You can start from there and adapt it to your exact requirement:

CREATE OR REPLACE FUNCTION public.UpdateTx(
    IN pInstructionId character varying,
    IN pTxdata character varying,
    IN pTxdetail character varying,  -- not used in the function
    IN pTxstatus character varying,  
    IN pResid character varying,     -- not used in the function
    IN pTimestamp bigint            - - not used in the function
)
    RETURNS character varying
    LANGUAGE plpgsql
    VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$

BEGIN

    IF pInstructionId IS NULL THEN
        RAISE EXCEPTION 'instruction id is missing';
    END IF;

    IF pTxstatus IS NULL THEN
        RAISE EXCEPTION 'tx status is missing';
    END IF;

    UPDATE transactions 
    SET txstatus = pTxstatus, txData = COALESCE(pTxdata, txData) 
    WHERE instructionid = pInstructionId;

    RETURN 1;  -- put something more meaningful here

END;
$BODY$;

You would invoke it as follows:

select UpdateTx(
    '123'::varchar, 
    'test'::varchar, 
    'test'::varchar, 
    'test'::varchar, 
    'test2'::varchar, 
    4124::bigint
)

Demo on DB Fiddle

edited Jul 20, 2020 at 14:06

answered Jul 20, 2020 at 13:59

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

There is no need for dynamic SQL to begin with update transactions set txtstatus = pTxstatus, txdata = pTxdata ... would work just fine

GMB Over a year ago

@a_horse_with_no_name: ah yes, that's even simpler... I changed my answer accordingly. Thank you.

johnhckuo Over a year ago

Thank you for ur answer! I really learned a lot :) Just one more question, you mentioned in my case it doesn't really need dynamic SQL. Is it because it can be solved by using the built-in COALESCE function? Thanks :D

Collectives™ on Stack Overflow

Postgresql function invocation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related