parsed.org

Tips by tag: types

Create a custom type which describes the structure of the rows returned:

CREATE TYPE func_return_type AS (column_name column_type, ...);

Then create the function:

CREATE OR REPLACE FUNCTION some_func (param_type, ...)
RETURNS SETOF func_return_type AS '
 SELECT columns FROM table WHERE condition;
' LANGUAGE 'SQL';
customfunctionsplpgsqlpostgresqlsqltypes

Use the form ::type to ensure that a value is cast to the proper type:

SELECT * FROM table WHERE column = 5::bigint;

In this case, column is indexed and is a bigint. The index will not be used if the query planner doesn't encounter a value of the index type. Note: this only applies in PostgreSQL 7.x.

gotchaindexperformancepostgresqlsqlsyntaxtypes
RSS