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

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
– Stefanov.sm, Commented Jan 7, 2022 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
– o..o, Commented Jan 7, 2022 at 12:00

JGH · Accepted Answer · 2022-01-07 12:15:04Z

1

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)

answered Jan 7, 2022 at 12:15

JGH

18.2k5 gold badges41 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related