[SOLVED] How to use stored procedure parameter as the select column value in Postgres?

How to use stored procedure parameter as the select column value in Postgres?

I'm trying to write a stored procedure in Postgres where I would use the parameter passed to the SP as a column value (not the column name) in a SELECT:

CREATE OR REPLACE PROCEDURE create_log
    (_contractCode varchar,
    _signedOn timestamp,
    _contractAmount numeric,
    _feePercentage numeric,
    _fee numeric,
    _projectCode varchar,
    _contractType varchar)
AS $$

BEGIN
    
INSERT INTO Contracts (ContractCode, SignedOn, ContractAmount, FeePercentage, Fee, ProjectId, ContractType)
SELECT _contractCode, _signedOn, _contractAmount, _feePercentage, _fee, p.Id AS ProjectId, _contractType
  FROM Projects p WHERE p.Code = _projectCode LIMIT 1;
END;
$$
LANGUAGE plpgsql ;

When I call it:

CALL public.create_log("contractcode",'2021-12-24T02:55:39',1000.7,3.2,3.232,'test','New');

I'm getting

SQL Error [42703]: ERROR: column "contractcode" does not exist Position: 24

which means it is trying to use the parameter value as the column name. I want to use the parameter value as the value returned from the SELECT.

Thanks

Solution

The problem is how you are calling the function, not within the function.

You are putting double quotes around the contract_code, so a column name is expected and it can't be found. You must use single quotes

CALL public.create_log('contractcode','2021-12-24T02:55:39',1000.7,3.2,3.232,'test','New');

select "a";
ERROR:  column "a" does not exist
LINE 1: select "a";
               ^
select 'b';
 ?column?
----------
 b
(1 row)