Postgresql - error returned more than one row from function

Question

My first post in Stackoverflow. I am trying to learn Postgresql (12) "on the job" and seem to be stuck on a rather simple issue. I have a simple database with 12 rows. In one column (int) there is a 4-digit number [representing a year]. I am trying to create a generated always column that takes the Current Date, subtract the previously mentioned int column to get an Age (int).

What am I missing. There is surely a simple solution.

Thanks James

After several failed attempts, I realize that a function should work. Here is the simple Function I created. The function works but seems to be returning more than 1 row. I just need the Asset_Age generated always column to show the age of the asset: [inservice is the existing int column that has the 4 digit (ex. 1963)]

--Function get_age

CREATE OR REPLACE FUNCTION amp.get_age()
RETURNS int
AS $CODE$
BEGIN
    RETURN extract (year from current_date)::int - inservice from amp.amp_pumpstations;
    
End
$CODE$
LANGUAGE PLPGSQL IMMUTABLE.

--------------

--create generated always column
ALTER TABLE IF EXISTS amp.amp_pumpstations
    ADD COLUMN asset_age integer GENERATED ALWAYS AS ((amp.get_age())) STORED;
    
-------------

ERROR:  query "SELECT extract(year from current_date)::int - inservice from amp.amp_pumpstations" returned more than one row
CONTEXT:  PL/pgSQL function amp.get_age() line 3 at RETURN
SQL state: 21000

`- inservice from amp.amp_pumpstations` which row from the table? I'm guessing you have some idea of "current row" but that doesn't mean anything in the function. Try writing a function that takes `inservice` and does a simple calculation. That could be a plain SQL function too. — Richard Huxton, Jun 07 '23 at 15:29

Patrick · Answer 1 · 2023-06-09T16:08:24.933

CURRENT_DATE being a STABLE function (value remains constant in a single statement), you should not use that in an IMMUTABLE function (function result is always the same for the same function arguments). It may "work" today but next year you likely have a problem because the function may have been "optimised". By extension, you cannot use a GENERATED ALWAYS column because that needs an IMMUTABLE function.

On top of that, the logic is faulty in that the asset_age is calculated when the row is inserted in the table or its underlying columns updated so as time moves on your asset ages go out of sync.

You can most easily solve your issue by dropping column asset_age from your table and create a view that calculates the value whenever you need it:

CREATE VIEW amp.amp_pumpstations_age AS
    SELECT *, extract(year from current_date) - inservice AS asset_age
    FROM amp.amp_pumpstations;

score 0 · Answer 2 · answered Jun 07 '23 at 15:18

0

This can be done using RETURN QUERY

CREATE OR REPLACE FUNCTION get_age()
RETURNS SETOF integer
AS $CODE$
BEGIN
    RETURN QUERY select extract (year from current_date)::int - inservice 
    from amp_pumpstations;
    
End
$CODE$
LANGUAGE PLPGSQL IMMUTABLE

Demo here

answered Jun 07 '23 at 15:18

SelVazi

10,028
2
13
29

1

This may "work" but it is incorrect, because `CURRENT_DATE` is not `IMMUTABLE`. As soon as next year rolls around you may have an "optimised" function that gives wrong results. – Patrick Jun 07 '23 at 18:30

Postgresql - error returned more than one row from function

2 Answers2