CODEWITHSUNDEEP
sqlpostgresql
Xr2206
Xr2206

Reputation: 13

How to insert into return table values if no value is found from select

I have current function

CREATE OR REPLACE FUNCTION G_test(
    i_tag_id integer,
    timestamp_from timestamp,
    timestamp_to timestamp
) 
    returns table (
        o_value kepware_messages.value%type,
        tag_id integer,
        created_at timestamp
    ) 
    language plpgsql
as $$
begin
    return query 
        (SELECT kepware_messages.value, kepware_messages.tag_id,kepware_messages.created_at FROM kepware_messages where kepware_messages.tag_id = i_tag_id and kepware_messages.created_at>=timestamp_from and kepware_messages.created_at<=timestamp_to)
        UNION
        (SELECT kepware_messages.value, kepware_messages.tag_id, kepware_messages.created_at FROM kepware_messages where kepware_messages.tag_id = i_tag_id and kepware_messages.created_at<timestamp_from ORDER BY kepware_messages.created_at DESC LIMIT 1)
        UNION
        (SELECT kepware_messages.value, kepware_messages.tag_id, kepware_messages.created_at FROM kepware_messages where kepware_messages.tag_id = i_tag_id and kepware_messages.created_at>timestamp_to ORDER BY kepware_messages.created_at ASC LIMIT 1)
        ORDER BY created_at DESC;
end;$$

I would like to do the following if in third select from union no table entry is found, to enter my own values. In such case I would enter

o_value = 'false';
tag_id = i_tag_id;
created_at = timestamp_from;

With help of google I found that you can use

if not found then

Though I am not yet sure how to implement it into union, however I can not find how to insert your own row of data into return table.

Also shouldn't this function return a different kind of table? It looks to me like everything is in same column. https://i.ibb.co/tMwnXQ2/1.jpg Sorry, I can't post images yet.

Upvotes: 0

Views: 181

Answers (1)

Serg
Serg

Reputation: 22811

Return a row from the table or a constructed row if no requested rows exist in the table.

  SELECT t.value, t.tag_id, t.created_at
  FROM
    (SELECT 1 rn, km.value, km.tag_id, km.created_at 
    FROM kepware_messages km 
    WHERE km.tag_id = i_tag_id and km.created_at>timestamp_to 
    ORDER BY km.created_at ASC 
    LIMIT 1

    UNION ALL

    SELECT 2 rn, 'false' value, i_tag_id tag_id , timestamp_from created_at
 ) t
 ORDER BY rn
 LIMIT 1

Replace the third select from your union with this one.

Upvotes: 1

Related Questions