0

I am new to Postgres functions and have a table with the following structure:

CREATE TABLE options.options (
  delta double precision,
  gamma double precision,
  rho double precision,
  theta double precision,
  impvol double precision,
  value double precision,
  vega double precision,
  id bigserial NOT NULL,
  date bigint,
  ticker text,
  callput text,
  chg double precision,
  maturity integer,
  symbol text,
  strike double precision,
  implied double precision,
  last double precision,
  vol double precision,
  ask double precision,
  bid double precision,
  CONSTRAINT options_pkey PRIMARY KEY (id)
);

And I am trying to build the following function:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS SETOF varchar(250) AS -- declare return type!
$BODY$
BEGIN -- required for plpgsql

   RETURN QUERY
    select maturity,avg(Implied) from options.options 
    where ticker=arg_ticker and date=arg_date and strike/arg_underlying>arg_lower 
    and strike/arg_underlying<arg_higher group by maturity order by maturity asc;

END; -- required for plpgsql
$BODY$ LANGUAGE plpgsql;

The issue I am having is the following error when trying to run the following query:

Query

select * from generate_term_structure_by_moneyness('TQQQ',20151221,120.2699,.98,1.02)

Error

ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type character varying in column 1.
CONTEXT:  PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY
********** Error **********

ERROR: structure of query does not match function result type
SQL state: 42804
Detail: Returned type integer does not match expected type character varying in column 1.
Context: PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY

I believe the error is coming from returns setof but I am unsure how to cater to this result type.

In pseudo code the returned data is expected in the form of:

[int,float; int, float; ...]
2

Your query returns two columns (one integer, one float), but the function is defined as returning a table with a single varchar column.

You need to change your function definition to:

RETURNS table(maturity integer, avg_implied numeric) 

to match the query result.

returns setof and returns table are the same thing but returns table lets you define names for each column in the result.


You also don't need PL/pgSQL for this, a simple SQL function will do just fine:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS table(maturity integer, avg_implied numeric) 
$BODY$
    select maturity,avg(Implied) 
    from options.options 
    where ticker=arg_ticker 
      and date=arg_date 
      and strike/arg_underlying > arg_lower 
      and strike/arg_underlying < arg_higher 
    group by maturity 
    order by maturity asc;
$BODY$ 
LANGUAGE sql;

You probably also want to re-think the usage of the approximate data type double:
http://floating-point-gui.de/


date is a horrible name for a column. For one because it is also a keyword, but more importantly it doesn't document the column's content. Is that a "start date"? An "end date"? A "due date"? A "registration date"? A "change date"? A "creation date"? And even more so if you don't actually store a date in that column but an integer value. What kind of date is the value 42 supposed to be?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.