1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
|
-- PostgreSQL catalog extensions for ODBC compatibility
-- $Header: /cvsroot/psqlodbc/psqlodbc/odbc.sql,v 1.9 2006/04/08 16:30:02 dpage Exp $
-- ODBC functions are described here:
-- <http://msdn.microsoft.com/library/en-us/odbc/htm/odbcscalar_functions.asp>
-- Note: If we format this file consistently we can automatically
-- generate a corresponding "drop script". Start "CREATE" in the first
-- column, and keep everything up to and including the argument list on
-- the same line. See also the makefile rule.
-- String Functions
-- ++++++++++++++++
--
-- Built-in: ASCII, BIT_LENGTH, CHAR_LENGTH, CHARACTER_LENGTH, LTRIM,
-- OCTET_LENGTH, POSITION, REPEAT, RTRIM, SUBSTRING
-- Missing: DIFFERENCE, REPLACE, SOUNDEX, LENGTH (ODBC sense)
-- Keyword problems: CHAR
-- CHAR(code)
CREATE OR REPLACE FUNCTION "char"(integer) RETURNS text AS '
SELECT chr($1);
' LANGUAGE SQL;
-- CONCAT(string1, string2)
CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text AS '
SELECT $1 || $2;
' LANGUAGE SQL;
-- INSERT(string1, start, len, string2)
CREATE OR REPLACE FUNCTION insert(text, integer, integer, text) RETURNS text AS '
SELECT substring($1 from 1 for $2 - 1) || $4 || substring($1 from $2 + $3);
' LANGUAGE SQL;
-- LCASE(string)
CREATE OR REPLACE FUNCTION lcase(text) RETURNS text AS '
SELECT lower($1);
' LANGUAGE SQL;
-- LEFT(string, count)
CREATE OR REPLACE FUNCTION left(text, integer) RETURNS text AS '
SELECT substring($1 for $2);
' LANGUAGE SQL;
-- LOCATE(substring, string[, start])
CREATE OR REPLACE FUNCTION locate(text, text) RETURNS integer AS '
SELECT position($1 in $2);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION locate(text, text, integer) RETURNS integer AS '
SELECT position($1 in substring($2 from $3)) + $3 - 1;
' LANGUAGE SQL;
-- RIGHT(string, count)
CREATE OR REPLACE FUNCTION right(text, integer) RETURNS text AS '
SELECT substring($1 from char_length($1) - $2 + 1);
' LANGUAGE SQL;
-- SPACE(count)
CREATE OR REPLACE FUNCTION space(integer) RETURNS text AS '
SELECT repeat('' '', $1);
' LANGUAGE SQL;
-- UCASE(string)
CREATE OR REPLACE FUNCTION ucase(text) RETURNS text AS '
SELECT upper($1);
' LANGUAGE SQL;
-- Numeric Functions
-- +++++++++++++++++
--
-- Built-in: ABS, ACOS, ASIN, ATAN, ATAN2, COS, COT, DEGRESS, EXP,
-- FLOOR, MOD, PI, RADIANS, ROUND, SIGN, SIN, SQRT, TAN
-- Missing: LOG (ODBC sense)
-- CEILING(num)
CREATE OR REPLACE FUNCTION ceiling(numeric) RETURNS numeric AS '
SELECT ceil($1);
' LANGUAGE SQL;
-- LOG10(num)
CREATE OR REPLACE FUNCTION log10(double precision) RETURNS double precision AS '
SELECT log($1);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION log10(numeric) RETURNS numeric AS '
SELECT log($1);
' LANGUAGE SQL;
-- POWER(num, num)
CREATE OR REPLACE FUNCTION power(double precision, double precision)
RETURNS double precision AS '
SELECT pow($1, $2);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION power(numeric, numeric)
RETURNS numeric AS '
SELECT pow($1, $2);
' LANGUAGE SQL;
-- RAND([seed])
CREATE OR REPLACE FUNCTION rand() RETURNS double precision AS '
SELECT random();
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION rand(double precision) RETURNS double precision AS '
SELECT setseed($1);
SELECT random();
' LANGUAGE SQL;
-- TRUNCATE(num, places)
CREATE OR REPLACE FUNCTION truncate(numeric, integer) RETURNS numeric AS '
SELECT trunc($1, $2);
' LANGUAGE SQL;
-- Time, Date, and Interval Functions
-- ++++++++++++++++++++++++++++++++++
--
-- Built-in: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, NOW
-- Missing: none
CREATE OR REPLACE FUNCTION curdate() RETURNS date AS '
SELECT current_date;
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION curtime() RETURNS time with time zone AS '
SELECT current_time;
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION odbc_timestamp() RETURNS timestamp with time zone AS '
SELECT current_timestamp;
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION dayname(timestamp) RETURNS text AS '
SELECT to_char($1,''Day'');
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION dayofmonth(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(day FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION dayofweek(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(dow FROM $1) AS integer) + 1;
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION dayofyear(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(doy FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION hour(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(hour FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION minute(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(minute FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION month(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(month FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION monthname(timestamp) RETURNS text AS '
SELECT to_char($1, ''Month'');
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION quarter(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(quarter FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION second(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(second FROM $1) AS integer);
' LANGUAGE SQL;
/*
-- The first argument is an integer constant denoting the units
-- of the second argument. Until we know the actual values, we
-- cannot implement these. - thomas 2000-04-11
xCREATE OR REPLACE FUNCTION timestampadd(integer, integer, timestamp)
RETURNS timestamp AS '
SELECT CAST(($3 + ($2 * $1)) AS timestamp);
' LANGUAGE SQL;
xCREATE OR REPLACE FUNCTION timestampdiff(integer, integer, timestamp)
RETURNS timestamp AS '
SELECT CAST(($3 + ($2 * $1)) AS timestamp);
' LANGUAGE SQL;
*/
CREATE OR REPLACE FUNCTION week(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(week FROM $1) AS integer);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION year(timestamp) RETURNS integer AS '
SELECT CAST(EXTRACT(year FROM $1) AS integer);
' LANGUAGE SQL;
-- System Functions
-- ++++++++++++++++
--
-- Built-in: USER
-- Missing: DATABASE, IFNULL
CREATE OR REPLACE FUNCTION odbc_user() RETURNS text AS '
SELECT CAST(current_user AS TEXT);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION odbc_current_user() RETURNS text AS '
SELECT CAST(current_user AS TEXT);
' LANGUAGE SQL;
CREATE OR REPLACE FUNCTION odbc_session_user() RETURNS text AS '
SELECT CAST(session_user AS TEXT);
' LANGUAGE SQL;
|