Question 185049 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – error returned more than one row from function

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
Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search