Date: 2015-04-11 20:10:05 +0200
From: John <>
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)
CC: @njnes

Last updated: 2015-05-07 12:37:45 +0200

Comment 20782

Date: 2015-04-11 20:10:05 +0200
From: John <>

CREATE USER "trader" WITH PASSWORD '1234' NAME 'Market User' SCHEMA "sys";
CREATE SCHEMA "marketdata" AUTHORIZATION "trader";
ALTER USER "trader" SET SCHEMA "marketdata";

CREATE SEQUENCE marketdata.seq_quotes_1 AS INTEGER ;

CREATE TABLE marketdata.quotes (
"id" INTEGER NOT NULL DEFAULT next value for marketdata.seq_quotes_1,
"qtime" TIMESTAMP WITH TIME ZONE NOT NULL,
"sdate" TIMESTAMP WITH TIME ZONE,
"sym" VARCHAR(10) NOT NULL,
"cur" VARCHAR(10) NOT NULL,
"open" DOUBLE NOT NULL,
"high" DOUBLE NOT NULL,
"low" DOUBLE NOT NULL,
"close" DOUBLE NOT NULL,
"volume" DOUBLE,
CONSTRAINT quotes_id_pkey PRIMARY KEY ("id")
);

CREATE FUNCTION marketdata.ohlc_d2(psym text, since date, close_time time, till date)
RETURNS TABLE(sdate date, open double, high double, low double, close double, volume double)
BEGIN
DECLARE tsince DATE;

IF since is NULL THEN
	SET tsince = DATE '1990-01-01';
ELSE
	SET tsince = since;
END IF;

IF close_time is NULL THEN
	RETURN TABLE ( SELECT * FROM (
		WITH open(sdate, open, rnk) as (
				select sdate, open, row_number() over (partition by sdate order by qtime asc) as rnk from marketdata.quotes 
					where sym=psym and sdate is not NULL and cast(sdate as date) > tsince
			),
			close(sdate, close, rnk) as (
					select sdate, close, row_number() over (partition by sdate order by qtime desc) as rnk from marketdata.quotes
						where sym=psym and sdate is not NULL and cast(sdate as date) > tsince
			),
			hilw(sdate,high,low, volume) as (
					select sdate, max(high) as high, min(low) as low, sum(volume) from marketdata.quotes 
						where sym=psym and sdate is not NULL and cast(sdate as date) > tsince group by sdate
			)	
			select open.sdate, open.open, hilw.high, hilw.low, close.close , hilw.volume from open, close, hilw where open.rnk=1 and close.rnk=1 and open.sdate=close.sdate and open.sdate=hilw.sdate ORDER BY sdate DESC
		) AS x
	);
ELSE 
	RETURN TABLE ( SELECT * FROM (
		WITH open(sdate, open, rnk) as (
				select sdate, open, row_number() over (partition by sdate order by qtime asc) as rnk from marketdata.quotes 
					where sym=psym and sdate is not NULL and cast(sdate as date) > tsince
			),
			close(sdate, close, rnk) as (
					select sdate, close, row_number() over (partition by sdate order by qtime desc) as rnk from marketdata.quotes
						where sym=psym and sdate is not NULL and cast(sdate as date) > tsince
			),
			hilw(sdate,high,low, volume) as (
					select sdate, max(high) as high, min(low) as low, sum(volume) from marketdata.quotes 
						where sym=psym and sdate is not NULL and cast(sdate as date) > tsince group by sdate
			)	
			select open.sdate, open.open, hilw.high, hilw.low, close.close , hilw.volume from open, close, hilw where open.rnk=1 and close.rnk=1 and open.sdate=close.sdate and open.sdate=hilw.sdate ORDER BY sdate DESC
		) AS x
	);
END IF;

END;

Comment 20783

Date: 2015-04-12 00:13:46 +0200
From: John <>

Even simpler:

DROP FUNCTION marketdata.test(text);

CREATE FUNCTION marketdata.test(a text) RETURNS table(SOMEFIELD date)
BEGIN
IF a is NULL THEN
return table(
select cast(sdate as date) from marketdata.quotes limit 10
);
ELSE
return table(
select cast(sdate as date) from marketdata.quotes limit 10
);
END IF;
END;

Comment 20784

Date: 2015-04-12 00:36:58 +0200
From: John <>

One more case:

CREATE FUNCTION marketdata.test(a text) RETURNS table(SOMEFIELD double)
BEGIN
declare table t(f double);
select 0.0 into t;
return t;
END;

I wonder if somebody used mounted before?

Comment 20785

Date: 2015-04-12 00:38:56 +0200
From: John <>

CREATE FUNCTION marketdata.test(a text) RETURNS table(SOMEFIELD double)
BEGIN
declare table t(f double);
set t = table (select 0.0);
return t;
END;

Comment 20807

Date: 2015-04-17 13:02:57 +0200
From: @njnes

just tested your last bit of example.
set t = table(...) isn't part of the sql standard.
Change this into insert into t (select ..).

That runs and doesn't crash in my tests.

Comment 20810

Date: 2015-04-17 17:35:44 +0200
From: John <>

DROP FUNCTION marketdata.test(text);

CREATE FUNCTION marketdata.test(a text) RETURNS table(SOMEFIELD date)
BEGIN
IF a is NULL THEN
return table(
select cast(sdate as date) from marketdata.quotes limit 10
);
ELSE
return table(
select cast(sdate as date) from marketdata.quotes limit 10
);
END IF;
END;

Still crashes the DB.

Side question. Why statement in function: RETURN TABLE ( WITH .... SELECT ...) generates syntax error?

Comment 20823

Date: 2015-04-19 22:50:11 +0200
From: @njnes

found (and fixed) a problem with the type checking of the if then statement.

The with is currently not supported in the return, will be added in
the next feature release.