CODEWITHSUNDEEP
postgresql
user1602492
user1602492

Reputation:

I am having problems to use EXISTS or PERFORM in a function to find out whether a row exists or not

I found several examples with EXISTS and PERFORM, but none worked for what I want to do. Following is what I have and it works, returning {"success" : true, "balance" : "500.00"} :

CREATE TEMPORARY TABLE IF NOT EXISTS accounts (id text, balance text);
INSERT INTO accounts(id, balance) VALUES ('123', '500.00');

CREATE OR REPLACE FUNCTION pg_temp.get_balance(id text)
RETURNS json AS
$$
SELECT json_build_object('success', true, 'balance', balance)
FROM
    (
        SELECT balance FROM accounts WHERE id = id
    ) _;
$$
LANGUAGE 'sql' VOLATILE;

SELECT pg_temp.get_balance('123');

Of course, sometimes an account - e.g. SELECT pg_temp.get_balance('456'); - doesn't exist. Then I'd like to get sth. like {"success" : false}. Can somebody perhaps give me hint how this needs to be done?

Upvotes: 1

Views: 42

Answers (3)

Abelisto
Abelisto

Reputation: 15614

Using one small trick:

with accounts (id, balance) as (values(123, 500.00))
select * from (select) as dummy left join accounts on (id = 123);

It allows to return at least one row even if there is no data in the right hand table. Note that the filtering condition should be in the join on part instead of the where clause.

Then you can to convert the result to JSON:

with accounts (id, balance) as (values(123, 500.00))
select json_build_object('success', id is not null, 'balance', balance)
from (select) as dummy left join accounts on (id = 123);

You can to use case statement if you do not want to have "balance" key if requested account not exists:

with accounts (id, balance) as (values(123, 500.00))
select
    case
        when id is null then json_build_object('success', false)
        else json_build_object('success', true, 'balance', balance)
    end
from (select) as dummy left join accounts on (id = 456);

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42763

One way is

CREATE OR REPLACE FUNCTION pg_temp.get_balance(_id text)
RETURNS json AS
$$
select '{"success" : false}'::json where not exists(select 1 from accounts where id = _id)
union all
select json_build_object('success', true, 'balance', balance) FROM accounts WHERE id = _id
$$
LANGUAGE 'sql' VOLATILE;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use:

CREATE OR REPLACE FUNCTION pg_temp.get_balance(_id text)
RETURNS json AS
$$
SELECT json_build_object('success', _.balance IS NULL, 'balance', balance)
FROM (SELECT 1) s
LEFT JOIN LATERAL (SELECT balance FROM accounts WHERE id = _id) _ ON TRUE;
$$
LANGUAGE 'sql' VOLATILE;

db<>fiddle demo

Please note that your example is not working because of:

SELECT balance FROM accounts WHERE id = id -- always true
=> 
SELECT balance FROM accounts WHERE id = _id (changed parameter name)

Upvotes: 0

Related Questions