PostgreSQL: Mysterious error when trying to insert into table with timestamp

Question

I have the following stored procedure:

Create FUNCTION createidentity7(new_browser_id bigint, sn smallint, sn_id bigint,
     last_updated timestamp with time zone, cred_mask_pos integer)

  RETURNS integer AS

  $BODY$

DECLARE

    mask integer = 0;

BEGIN

    if (sn = 0 AND sn_id = 0) then

        Select COALESCE(max(i.cred_mask_pos)+1,0) into mask FROM 
                            identity i WHERE i.browser_id = @browser_id;

    end if;

    Insert into identity(browser_id, sn_id, last_updated, cred_mask_pos, sn)
    Values(@new_browser_id, @sn_id, @last_updated::timestamp with time zone,
                                                                mask, @sn);

    Return mask;

END;
$BODY$
Language 'plpgsql'

When I call it:

select createidentity7
          (0::bigint, 0::smallint, 0::bigint ,'2004-10-19 10:23:54+02', 0);

I get this error:

ERROR: operator does not exist: @ timestamp with time zone

SQL state: 42883 Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Context: PL/pgSQL function "createidentity7" line 7 at SQL statement

Does this ring a bell for anyone? I cannot find anything resembling this issue anywhere after quite some searching. Help would be much appreciated.

mu is too short · Accepted Answer · 2011-04-19 04:55:50Z

2

You don't need the @ signs to refer to variables in PLPGSQL, that syntax is from (AFAIK) T-SQL. The @ sign is used for absolute value in PostgreSQL and PostgreSQL is saying that it doesn't know how to take the absolute value of a timestamp. The @ works fine with the other parameters because they're numeric. Drop the @ signs and it should work.

answered Apr 19, 2011 at 4:55

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Mysterious error when trying to insert into table with timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related