How to pass a parameter and cast to a certain data type inside a function?

Question

I'm attempting to write a plpgsql function to insert a record into a table that expects some timestamps in certain columns. Here is the function:

create or replace function insert_slot(created_by varchar
                                     , version bigint
                                     , bsv_id bigint
                                     , start_date varchar) returns int as $$
declare
    last_id int := (select id from appointments order by id desc limit 1) + 1;

begin
    insert into appointments (
        id,
        created,
        created_by,
        version,
        bsv_id,
        usrn,
        start_date,
        end_date,
        status,
        request_priority_name,
        reservation_expiry,
        day_of_week
    )values (
        last_id,
        now(),
        created_by,
        version,
        bsv_id,
        'UN-' || last_id,
        to_timestamp(extract(epoch from timestamp @start_date)),
        to_timestamp(extract(epoch from timestamp '2017-2-12 10:30:00')),
        'OCCUPIED',
        'ROUTINE',
        to_timestamp(extract(epoch from timestamp '2017-3-19 10:30:00')),
        1
    );

    return last_id;
end;
$$ LANGUAGE plpgsql;

select * from insert_slot('Brad', 2, 70000, '2017-2-12 10:00:00');

This works fine when I am passing a literal string for the date format, but not for a parameter:

to_timestamp(extract(epoch from timestamp @start_date)),
to_timestamp(extract(epoch from timestamp '2017-2-12 10:30:00')),

When I try to use @start_date or start_date instead of the literal string I get this error:

ERROR:  column "timestamp" does not exist
LINE 21:    to_timestamp(extract(epoch from timestamp @start_date)),

Can someone enlighten me as to the correct syntax to use? I've found plenty of posts online about using parameters but can't find anything that addresses why I can't pass through the function parameter to epoch from timestamp.

Answer 1

1.

Do not use a @ character to prepend variables. That's SQL-Server or MySQL syntax (and maybe others) and illegal in Postgres SQL or PL/pgSQL code.

PL/pgSQL variable names follow the same rules as SQL identifiers. The manual:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

So @start_date is a syntax error.

2.

In this expression:

to_timestamp(extract(epoch from timestamp '2017-2-12 10:30:00')),

timestamp is the data type of the immediately following string literal.

But this notation is not allowed for run-time type conversions. So this is a syntax error:

to_timestamp(extract(epoch from timestamp start_date))

You can use an explicit type cast instead:

to_timestamp(extract(epoch from start_date::timestamp))

The manual:

The ::, CAST(), and function-call syntaxes can also be used to specify run-time type conversions of arbitrary expressions, as discussed in Section 4.2.9. To avoid syntactic ambiguity, the type 'string' syntax can only be used to specify the type of a simple literal constant.

---

In your particular case it would be smarter / cleaner to define the function parameter as date or timestamp to begin with - depends on what kind of data you plan to pass to the function. Your parameter name indicates a date, but your example indicates a timestamp.

Either way, you don't need to cast that later. EXTRACT() also accepts date and casts it to timestamp automatically.