PostgreSQL Functions By Example

← Back to document page

OverviewFunction BasicsBy ExamplePostgreSQL Functions By ExampleJoe GroupJanuary 20, 2012Joe ConwaySCALE10X-PGDayOverviewFunction BasicsBy ExampleIntroductionUsesVarietiesLanguage sWhat are Functions ?Full fledged SQL objectsMany other database objects are implemented with themFundamental part of PostgreSQL s system architectureCreated withCREATE FUNCTIONExecuted through normal SQLtarget-list:SELECT myfunc(f1) FROM foo;FROM clause:SELECT * FROM myfunc();WHERE clause:SELECT * FROM foo WHERE myfunc(f1) = 42;Joe ConwaySCALE10X-PGDayOverviewFunction BasicsBy ExampleIntroductionUsesVarietiesLanguage sHow are they Used?FunctionsOperatorsData typesIndex methodsCastsTriggersAggregatesJoe ConwaySCALE10X-PGDayOverviewFunction BasicsBy ExampleIntroductionUsesVarietiesLanguage sWhat Forms Can They Take? PostgreSQL provides four kinds of Functions :SQLProcedural LanguagesInternalC-languageArgumentsBase , composite, or combinationsScalar or arrayPseudo or polymorphicVARIADICIN/OUT/INOUTReturnSin gleton or set (SETOF)Base or composite typePseudo or ConwaySCALE10X-PGDayOverviewFunction BasicsBy ExampleIntroductionUsesVarietiesLanguage sSQL FunctionsBehaviorExecutes an arbitrary list of SQL statements separated bysemicolonsLast statement may be INSERT, UPDATE, or DELETE withRETURN

By Example Introduction Uses Varieties Languages Procedural Languages User-de ned functions Written in languages besides SQL and C Task is passed to a special handler that knows the details of the language Handler could be self-contained (e.g. PL/pgSQL) Handler could be dynamically loaded (e.g. PL/Perl)

  Example, Perl, By example