1 -- PostgreSQL catalog extensions for ODBC compatibility
2 -- $Header: /cvsroot/psqlodbc/psqlodbc/odbc.sql,v 1.9 2006/04/08 16:30:02 dpage Exp $
4 -- ODBC functions are described here:
5 -- <http://msdn.microsoft.com/library/en-us/odbc/htm/odbcscalar_functions.asp>
7 -- Note: If we format this file consistently we can automatically
8 -- generate a corresponding "drop script". Start "CREATE" in the first
9 -- column, and keep everything up to and including the argument list on
10 -- the same line. See also the makefile rule.
16 -- Built-in: ASCII, BIT_LENGTH, CHAR_LENGTH, CHARACTER_LENGTH, LTRIM,
17 -- OCTET_LENGTH, POSITION, REPEAT, RTRIM, SUBSTRING
18 -- Missing: DIFFERENCE, REPLACE, SOUNDEX, LENGTH (ODBC sense)
19 -- Keyword problems: CHAR
23 CREATE OR REPLACE FUNCTION "char"(integer) RETURNS text AS '
28 -- CONCAT(string1, string2)
29 CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text AS '
34 -- INSERT(string1, start, len, string2)
35 CREATE OR REPLACE FUNCTION insert(text, integer, integer, text) RETURNS text AS '
36 SELECT substring($1 from 1 for $2 - 1) || $4 || substring($1 from $2 + $3);
41 CREATE OR REPLACE FUNCTION lcase(text) RETURNS text AS '
46 -- LEFT(string, count)
47 CREATE OR REPLACE FUNCTION left(text, integer) RETURNS text AS '
48 SELECT substring($1 for $2);
52 -- LOCATE(substring, string[, start])
53 CREATE OR REPLACE FUNCTION locate(text, text) RETURNS integer AS '
54 SELECT position($1 in $2);
56 CREATE OR REPLACE FUNCTION locate(text, text, integer) RETURNS integer AS '
57 SELECT position($1 in substring($2 from $3)) + $3 - 1;
61 -- RIGHT(string, count)
62 CREATE OR REPLACE FUNCTION right(text, integer) RETURNS text AS '
63 SELECT substring($1 from char_length($1) - $2 + 1);
68 CREATE OR REPLACE FUNCTION space(integer) RETURNS text AS '
69 SELECT repeat('' '', $1);
74 CREATE OR REPLACE FUNCTION ucase(text) RETURNS text AS '
82 -- Built-in: ABS, ACOS, ASIN, ATAN, ATAN2, COS, COT, DEGRESS, EXP,
83 -- FLOOR, MOD, PI, RADIANS, ROUND, SIGN, SIN, SQRT, TAN
84 -- Missing: LOG (ODBC sense)
88 CREATE OR REPLACE FUNCTION ceiling(numeric) RETURNS numeric AS '
94 CREATE OR REPLACE FUNCTION log10(double precision) RETURNS double precision AS '
97 CREATE OR REPLACE FUNCTION log10(numeric) RETURNS numeric AS '
103 CREATE OR REPLACE FUNCTION power(double precision, double precision)
104 RETURNS double precision AS '
107 CREATE OR REPLACE FUNCTION power(numeric, numeric)
114 CREATE OR REPLACE FUNCTION rand() RETURNS double precision AS '
117 CREATE OR REPLACE FUNCTION rand(double precision) RETURNS double precision AS '
123 -- TRUNCATE(num, places)
124 CREATE OR REPLACE FUNCTION truncate(numeric, integer) RETURNS numeric AS '
125 SELECT trunc($1, $2);
129 -- Time, Date, and Interval Functions
130 -- ++++++++++++++++++++++++++++++++++
132 -- Built-in: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, NOW
136 CREATE OR REPLACE FUNCTION curdate() RETURNS date AS '
140 CREATE OR REPLACE FUNCTION curtime() RETURNS time with time zone AS '
144 CREATE OR REPLACE FUNCTION odbc_timestamp() RETURNS timestamp with time zone AS '
145 SELECT current_timestamp;
148 CREATE OR REPLACE FUNCTION dayname(timestamp) RETURNS text AS '
149 SELECT to_char($1,''Day'');
152 CREATE OR REPLACE FUNCTION dayofmonth(timestamp) RETURNS integer AS '
153 SELECT CAST(EXTRACT(day FROM $1) AS integer);
156 CREATE OR REPLACE FUNCTION dayofweek(timestamp) RETURNS integer AS '
157 SELECT CAST(EXTRACT(dow FROM $1) AS integer) + 1;
160 CREATE OR REPLACE FUNCTION dayofyear(timestamp) RETURNS integer AS '
161 SELECT CAST(EXTRACT(doy FROM $1) AS integer);
164 CREATE OR REPLACE FUNCTION hour(timestamp) RETURNS integer AS '
165 SELECT CAST(EXTRACT(hour FROM $1) AS integer);
168 CREATE OR REPLACE FUNCTION minute(timestamp) RETURNS integer AS '
169 SELECT CAST(EXTRACT(minute FROM $1) AS integer);
172 CREATE OR REPLACE FUNCTION month(timestamp) RETURNS integer AS '
173 SELECT CAST(EXTRACT(month FROM $1) AS integer);
176 CREATE OR REPLACE FUNCTION monthname(timestamp) RETURNS text AS '
177 SELECT to_char($1, ''Month'');
180 CREATE OR REPLACE FUNCTION quarter(timestamp) RETURNS integer AS '
181 SELECT CAST(EXTRACT(quarter FROM $1) AS integer);
184 CREATE OR REPLACE FUNCTION second(timestamp) RETURNS integer AS '
185 SELECT CAST(EXTRACT(second FROM $1) AS integer);
189 -- The first argument is an integer constant denoting the units
190 -- of the second argument. Until we know the actual values, we
191 -- cannot implement these. - thomas 2000-04-11
192 xCREATE OR REPLACE FUNCTION timestampadd(integer, integer, timestamp)
193 RETURNS timestamp AS '
194 SELECT CAST(($3 + ($2 * $1)) AS timestamp);
197 xCREATE OR REPLACE FUNCTION timestampdiff(integer, integer, timestamp)
198 RETURNS timestamp AS '
199 SELECT CAST(($3 + ($2 * $1)) AS timestamp);
203 CREATE OR REPLACE FUNCTION week(timestamp) RETURNS integer AS '
204 SELECT CAST(EXTRACT(week FROM $1) AS integer);
207 CREATE OR REPLACE FUNCTION year(timestamp) RETURNS integer AS '
208 SELECT CAST(EXTRACT(year FROM $1) AS integer);
216 -- Missing: DATABASE, IFNULL
218 CREATE OR REPLACE FUNCTION odbc_user() RETURNS text AS '
219 SELECT CAST(current_user AS TEXT);
222 CREATE OR REPLACE FUNCTION odbc_current_user() RETURNS text AS '
223 SELECT CAST(current_user AS TEXT);
226 CREATE OR REPLACE FUNCTION odbc_session_user() RETURNS text AS '
227 SELECT CAST(session_user AS TEXT);