PostgreSQL/PLPGSQL Procedure, that returns variable as column

Question

I'm trying to write a procedure, which inserts a row in a table and then returns an assigned id.

Table example:

CREATE TABLE sometable (
UUID Id,
varchar field1,
varchar field2)

My function:

CREATE OR REPLACE FUNCTION insert_sometable(varchar pfield1, varchar pfield2)
  RETURNS UUID AS  $$
DECLARE
pid UUID;
BEGIN
pid=uuid_generate();
insert into sometable(id, field1, field2) values (pid, pfield1, pfield2);
return pid;
END;
$$
LANGUAGE plpgsql;

And i want it to use it like that:

select pid from insert_sometable('_field1_value', '_field2_value')

But now i got no column name of its result, so i can't access it in my ESB service. I want column called "pid" in result set. Seems like easy task, but i didn't found any simple solution. Any suggestions?

Craig Ringer · Accepted Answer · 2014-09-04 09:27:33Z

1

I think you're just trying to alias the returned column name. If so:

select pid 
from insert_sometable('_field1_value', '_field2_value') AS inserted_row(pid);

PostgreSQL permits you to write shorthand for this for functions that return only one column:

select pid 
from insert_sometable('_field1_value', '_field2_value') pid;

answered Sep 4, 2014 at 9:27

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

pozs · Accepted Answer · 2014-09-04 12:59:21Z

0

You shouldn't use a function whose return value is atomic (not a row) in the FROM clause (it's possible, but makes your query less readable). You can add an alias in the SELECT clause too:

select insert_sometable('_field1_value', '_field2_value') as pid;

edited Sep 4, 2014 at 12:59

answered Sep 4, 2014 at 9:40

pozs

36.6k5 gold badges61 silver badges66 bronze badges

1 Comment

CaptainFreedom Over a year ago

Thanks, pozs. You are so fast.

Collectives™ on Stack Overflow

PostgreSQL/PLPGSQL Procedure, that returns variable as column

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related