Rename the column name of a stored function

Question

I've got a postgresql stored procedure, which is returning an integer. When I call that function, the result is returned with the function name as column name.

For example the name of the function is: "add-person". The column name, when invoking the function, is "add-person".

Is there a way to make the database return the integer with a self-choosen column name? For example "id"?

I think it is pretty easy, but I currently miss the forests for the trees..

Edit: What i'd missed to tell, is that the return value is a variable, like so:

CREATE OR REPLACE FUNCTION "scheme"."add-person"(arggivenname character varying, argfamilyname character varying) RETURNS integer AS $BODY$ DECLARE varResponse integer; BEGIN -- Operations before INSERT INTO "scheme"."table" ( given_name, family_name ) VALUES ( arggivenname, argfamilyname ) RETURNING "id" INTO varResponse; -- Operations after RETURN varResponse; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Hello a_horse_with_no_name, I can not copy all the code. But I hope the code above helps. I already thought something like RETURN varResponse AS "id" would help, but in fact, it did not. — Steffen
– Steffen, Commented Feb 10, 2015 at 12:56

Bine · Accepted Answer · 2015-02-10 12:17:18Z

2

You can us the AS statement for that. That means:

 Select add-person() AS yourcolumnname

answered Feb 10, 2015 at 12:17

Bine

3941 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Steffen Over a year ago

Hello Bine, thank you for your response! But I would like to make the change at the database side :/

Bine Over a year ago

Why do you want to change it on database side? You told that you get just an integer value back. What do you want to do with the renamed column name?

Steffen Over a year ago

That is true. There is a php script, which expects to get the value via pg_fetch_assoc (associative array). It is searching for the column name "id". Changing the php script isn't possible. That is the reason, why I'm asking :/

Bine Over a year ago

How do you call your add-person function and pg_fetch_assoc? Is it something like $result = pg_query($conn, "SELECT add-person()"); , pg_fetch_assoc($result)? In this case you can use my solution with the alias, because $result is a "table" with columnname id and your integer value; an example for that is here: php.net/manual/de/function.pg-fetch-assoc.php

Steffen Over a year ago

The request: $response = pg_query($connection, "SELECT * FROM \"scheme\".\"add-person\"($givenName, $familyName)");. The interpretation: $row = pg_fetch_assoc($response); return $row['id']; I am sorry, if this causes annoyance

|

Clodoaldo Neto · Accepted Answer · 2015-02-10 13:20:37Z

To have a named column from a function it is necessary to create a type and return that type from the function

create type mytype as (mycolumn integer); create or replace function ri() returns mytype as $$ select 1; $$ language sql; select * from ri(); mycolumn ---------- 1

Edit

Or much simpler without the type creation as in @pozs comment:

create or replace function ri(out mycolumn integer) as $$ select 1; $$ language sql;

Nice trick, but select ri() will give a "strange" (=unexpected) result in this case.
@a_horse The real solution is to give the column an alias as in Bine's answer which he refused because he did not pay attention to it.
@ClodoaldoNeto a single out mycolumn integer parameter (& without the returns clause) the function can work both ways. However, if called as select ri() will still return the column as ri.

Collectives™ on Stack Overflow

Rename the column name of a stored function

2 Answers 2

8 Comments

Edit

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Edit

3 Comments

Related