CODEWITHSUNDEEP
postgresqlplpgsql
sam.bishop
sam.bishop

Reputation: 400

name of value returned from PostgreSQL function

I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out.

I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a function. (Unfortunately, all of the examples I've found do a "SELECT * ...")

Here's the function I'm trying to call:

CREATE OR REPLACE FUNCTION aSchema.aFunction(var1 text, var2 accountidentifier, ...)
  RETURNS int8
AS
$BODY$
  DECLARE
    retJobId    BIGINT;
BEGIN
    INSERT INTO aSchema.aTable (var1, var2, ...)
    VALUES (var1, var2, ...)
    RETURNING jobId INTO retJobId;

    PERFORM aSchema.anotherFunction(retJobId, ...);

    RETURN retJobId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I've tried SELECT retJobId FROM aSchema.aFunction(...), but I get ERROR: column "retjobid" does not exist. Replacing "retJobId" with "*" works, but, like I said, I would like to avoid doing that.

Upvotes: 2

Views: 530

Answers (2)

klin
klin

Reputation: 121604

You can choose between:

select aschema.afunction() as my_name;               -- like in IMSoP's answer
select my_name from aschema.afunction() as my_name;  -- with alias
select afunction from aschema.afunction();           -- with function name

If you add aschema to search path, you can omit schema identifier:

set search_path to public, aschema;
select afunction() as my_name;
select my_name from afunction() as my_name;
select afunction from afunction();

Upvotes: 3

IMSoP
IMSoP

Reputation: 97718

Since the function returns a scalar value (a single integer) you don't need to select FROM it at all, just select it as a single column, and give it a column alias so it's easily accessible in the calling code:

SELECT aSchema.aFunction(...) AS retJobId;

If this looks odd, consider it's just the same as selecting a mathematical expression, such as:

SELECT 6 * 9 AS answer;

In Oracle, you'd need a FROM clause, such as FROM dual, but Postgres doesn't require this.

Upvotes: 2

Related Questions