Postgres function returning a string from a select statement-CodePudding

I am trying to get this function to return a string that comes from the SELECT statement.

CREATE OR REPLACE FUNCTION dbo.fnRepID (pram_ID BIGINT)
RETURNS varchar AS $$
DECLARE 
    pram varchar := '';
BEGIN
    SELECT pram =  pram || (Case COALESCE(a.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END )
        || (Case COALESCE(b.Name, '' ) WHEN '' THEN '' ELSE b.Name || ' - ' END )
        || f.NAME || ';'
    FROM dbo.ClassRelationship a
        LEFT JOIN dbo.ClassRelationship b ON a.ParentClassID = b.ClassID and b.Type = 2 and a.Type = 1;
 
    RETURN pram;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION dbo.fnRepID (BIGINT) TO "postgres";

And this function creation seem to go through just fine, now I want to call this function with 0 as pram_ID but it's saying this basically no matter what I do.

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function dbo.fnrepid(bigint) line 5 at SQL statement
SQL statement "SELECT dbo.fnRepID(0)"
PL/pgSQL function inline_code_block line 2 at PERFORM
SQL state: 42601

I have tried a DO PERFORM block as I have seen similar examples DO $$ BEGIN PERFORM dbo.fnRepID(0); END $$;but I basically want to get the return value from this function somehow. If there's anything wrong with my procedures please let me know, as I am new to postgres. Thank you.

Edit: Would the following work if I want to append each row of the results into the same var? (can I use select into with the variable on both sides to append to itself?)

CREATE OR REPLACE FUNCTION dbo.fnRepID (pram_ID BIGINT)
RETURNS varchar AS $$
DECLARE 
    pram varchar := '';
BEGIN
    SELECT pram || (Case COALESCE(a.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END )
        || (Case COALESCE(b.Name, '' ) WHEN '' THEN '' ELSE b.Name || ' - ' END )
        || f.NAME || ';' INTO pram
    FROM dbo.ClassRelationship a
        LEFT JOIN dbo.ClassRelationship b ON a.ParentClassID = b.ClassID and b.Type = 2 and a.Type = 1;
 
    RETURN pram;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION dbo.fnRepID (BIGINT) TO "postgres";

CodePudding user response：

You're doing

SELECT pram = (…) FROM dbo.ClassRelationship a …;

where (…) is an expression that is evaluated and then compared to the current value of pram (which was initialised to an empty string). The query does nothing else, there is no destination for this boolean value (comparison result) it computes, you're getting an error.

You most likely meant to either perform an assignment

pram = SELECT (…) FROM dbo.ClassRelationship a …;

or use an INTO clause:

SELECT (…) INTO pram FROM dbo.ClassRelationship a …;

Notice that you don't even need pl/pgsql to do this. A plain sql function would do as well:

CREATE OR REPLACE FUNCTION dbo.fnRepID(pram_ID BIGINT) RETURNS varchar
LANGUAGE sql
STABLE
RETURN (
  SELECT
    '' ||
    (CASE COALESCE(a.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END) ||
    (CASE COALESCE(b.Name, '' ) WHEN '' THEN '' ELSE b.Name || ' - ' END) ||
    f.NAME ||
    ';'
  FROM dbo.ClassRelationship a
  LEFT JOIN dbo.ClassRelationship b ON a.ParentClassID = b.ClassID AND b.Type = 2 AND a.Type = 1
);