How do I return a single column in psotgres function?

Question

So, I have this schema:

node_mapping(node_id: UUID, node_name: text, related_ids: jsonb)

I wanted to write a postgres function for this query so that I get a List as the output in my JPA method in Springboot.

select CAST(node_id AS VARCHAR) node_id from node_mapping where related_ids::jsonb? _seller_id = true;

I wrote this function but it isn't working, please help me.

CREATE OR REPLACE FUNCTION find_node_id(_relation_id text)
 RETURNS TABLE(node_id text) AS
 $func$
    BEGIN
        RETURN QUERY select CAST(node_id AS VARCHAR) node_id from node_mapping where related_ids::jsonb? _relation_id = true;
    END;
 $func$ LANGUAGE plpgsql;

Please help me, what m I doing wrong?

What do you mean by not working? https://stackoverflow.com/help/how-to-ask — Simon Martinelli, Sep 30 '21 at 15:13
Please remember to disclose the verbatim error message you got for your next question. And always your version of Postgres. — Erwin Brandstetter, Sep 30 '21 at 15:56

Erwin Brandstetter · Answer 1 · 2021-09-30T15:53:53.583

It's unclear what's "not working", but I see an ambiguous column reference "node_id". Easily fixed by table-qualifying the column name:

CREATE OR REPLACE FUNCTION find_node_id(_relation_id text)
  RETURNS TABLE(node_id text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT n.node_id::text    -- HERE!
   FROM   node_mapping n
   WHERE  n.related_ids ? _relation_id;
END
$func$;

Note that node_id in the RETURNS TABLE declaration is implicitly visible as OUT parameter in the whole function body. So you need to disambiguate between it and the column with the same name.

How to return result of a SELECT inside a function in PostgreSQL?

I fixed a couple of other oddities (that would still work, though). Among other things, you don't need to cast related_ids::jsonb when the column is already type jsonb.

A final RETURN; is optional. The manual:

A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).

For the simple example, rather use a simpler, equivalent SQL function:

CREATE OR REPLACE FUNCTION find_node_id(_relation_id text)
  RETURNS TABLE(node_id text)
  LANGUAGE sql AS
$func$
SELECT n.node_id::text
FROM   node_mapping n
WHERE  n.related_ids ? _relation_id;
$func$;

Or, equivalent:

CREATE OR REPLACE FUNCTION find_node_id(_relation_id text, OUT node_id text)
  RETURNS SETOF record
  LANGUAGE sql AS
$func$
SELECT n.node_id::text
FROM   node_mapping n
WHERE  n.related_ids ? _relation_id;
$func$;

How do I return a single column in psotgres function?

1 Answers1