CODEWITHSUNDEEP
postgresql
cheshirecatalyst
cheshirecatalyst

Reputation: 379

need different return format from postgres function

I have a table

CREATE TABLE reward_transactions
(
  uid bigint NOT NULL,
  reward_type text NOT NULL,
  count bigint,
  last_update timestamp with time zone DEFAULT now()
)

And a function

CREATE FUNCTION store_reward_transaction(bigint, text) returns record
    LANGUAGE plpgsql
    AS $_$
declare
_record record;
begin
update reward_transactions set count = count + 1, last_update = now() where uid = $1 and reward_type = $2::text returning count, last_update::timestamp with time zone into _record;
if found then
return _record;
end if;
begin
insert into reward_transactions (uid, count, reward_type) values ($1, 1, $2::text) returning count, last_update::timestamp with time zone into _record;
return _record;
exception when unique_violation then
update reward_transactions set count = count + 1, last_update = now() where uid = $1 and reward_type = $2::text returning count, last_update::timestamp with time zone into _record;
return _record;
end;
end
$_$;

The question being how can i get this to not return rows: [ { store_reward_transaction: '(12,"2014-07-18 17:29:39.780207-05")' } ]and instead return something akin to what a select from the original table would have (ie column names intact), i have tried returning it as tablename%ROWTYPE, using a custom type and a few other things and i just can't seem to get the desired output. Using 9.3.3

Upvotes: 0

Views: 49

Answers (1)

Dwayne Towell
Dwayne Towell

Reputation: 8583

Try this:

SELECT * FROM store_reward_transaction(4,'blah') f(count bigint, last_update timestamp with time zone);

Upvotes: 1

Related Questions