Postgresql – Using Postgres param in a function

functionspostgresqltimestamp

I'm trying to create a few functions to make the SQL queries between my SQLite and Postgres DB's the same.

One thing I would like to do is change a timestamp to a double via a function such as:

create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM TIMESTAMP a);
$$ language sql;

Postgres however doesn't allow me to create this function. It errors out like so:

ERROR:  syntax error at or near "a"
LINE 2:  SELECT EXTRACT(EPOCH FROM TIMESTAMP a);

Would it be possible to create this function / use this as a param?

Best Answer

You are adding both Timestamp and a in extract function, just remove TIMESTAMP

create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM  a);
$$ language sql;

Related Solutions

Sql-server – Create Enumerated Function Parameter Type

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
  @datepart VARCHAR(32), -- does not need to be NVARCHAR
  @start    DATETIME,
  @end      DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @datepart 
      WHEN 'HOUR'   THEN DATEDIFF(HOUR,   @start, @end) 
      WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
      WHEN 'WEEK'   THEN DATEDIFF(WEEK,   @start, @end)
      ELSE               DATEDIFF(DAY,    @start, @end)
    END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart,

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

Msg 1023, Level 15, State 1, Line 2
Invalid parameter 1 specified for datediff.

Postgresql – How to create Select query function in the Postgresql

Function Execute like this And get my Answer

select * from functionname();

Best Answer

Related Solutions

Sql-server – Create Enumerated Function Parameter Type

Postgresql – How to create Select query function in the Postgresql

Related Question