0

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

1 Answer 1

1

Try this:

SELECT * FROM store_reward_transaction(4,'blah') f(count bigint, last_update timestamp with time zone);
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks that got me on the right track update your answer accordingly select * from store_reward_transaction(4, 'blah') as f(count bigint, last_update timestamp with time zone); and i will mark it, you original answer results in error: a column definition list is required for functions returning "record"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.