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

Question

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

Which parameters are column names and which are constant values to be inserted into table `contracts`? Please also note that unquoted names are converted to lowercase in Postgresql. — Stefanov.sm, Jan 07 '22 at 11:19
Of course there is no column named “ContractCode”. It is the value that I want to insert to the Contracts table, not any column name. All parameter names are preceeded by underscore. — o..o, Jan 07 '22 at 12:00
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS — , Jan 07 '22 at 12:23

score 1 · Accepted Answer · answered Jan 07 '22 at 12:15

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)

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

1 Answers1