Squeal.PostgreSQL.Definition.Function

create and drop functions

Create

createFunction

Arguments

:: (Has sch db schema, KnownSymbol fun, SListI args)

=> QualifiedAlias sch fun

function alias

-> NP (TypeExpression db) args

arguments

-> TypeExpression db ret

return type

-> FunctionDefinition db args ('Returns ret)

function definition

-> Definition db (Alter sch (Create fun ('Function (args :=> 'Returns ret)) schema) db)

Create a function.

>>> type Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4))
>>> :{
let
  definition :: Definition (Public '[]) (Public '["fn" ::: Fn])
  definition = createFunction #fn (int4 *: int4) int4 $
    languageSqlExpr (param @1 * param @2 + 1)
in printSQL definition
:}
CREATE FUNCTION "fn" (int4, int4) RETURNS int4 language sql as $$ SELECT * FROM (VALUES (((($1 :: int4) * ($2 :: int4)) + (1 :: int4)))) AS t ("ret") $$;

createOrReplaceFunction

Arguments

:: (Has sch db schema, KnownSymbol fun, SListI args)

=> QualifiedAlias sch fun

function alias

-> NP (TypeExpression db) args

arguments

-> TypeExpression db ret

return type

-> FunctionDefinition db args ('Returns ret)

function definition

-> Definition db (Alter sch (CreateOrReplace fun ('Function (args :=> 'Returns ret)) schema) db)

Create or replace a function. It is not possible to change the name or argument types or return type of a function this way.

>>> type Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4))
>>> :{
let
  definition :: Definition (Public '["fn" ::: Fn]) (Public '["fn" ::: Fn])
  definition =
    createOrReplaceFunction #fn
      (int4 *: int4) int4 $
      languageSqlExpr (param @1 @('Null 'PGint4) * param @2 @('Null 'PGint4) + 1)
in printSQL definition
:}
CREATE OR REPLACE FUNCTION "fn" (int4, int4) RETURNS int4 language sql as $$ SELECT * FROM (VALUES (((($1 :: int4) * ($2 :: int4)) + (1 :: int4)))) AS t ("ret") $$;

createSetFunction

Arguments

:: (Has sch db schema, KnownSymbol fun, SListI args, SListI rets)

=> QualifiedAlias sch fun

function alias

-> NP (TypeExpression db) args

arguments

-> NP (Aliased (TypeExpression db)) rets

return type

-> FunctionDefinition db args ('ReturnsTable rets)

function definition

-> Definition db (Alter sch (Create fun ('Function (args :=> 'ReturnsTable rets)) schema) db)

Create a set function.

>>> type Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])
>>> type Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4])
>>> :{
let
  definition :: Definition (Public '["tab" ::: Tab]) (Public '["tab" ::: Tab, "fn" ::: Fn])
  definition = createSetFunction #fn (int4 *: int4) (int4 `as` #ret) $
    languageSqlQuery (select_ ((param @1 * param @2 + #col) `as` #ret) (from (table #tab)))
in printSQL definition
:}
CREATE FUNCTION "fn" (int4, int4) RETURNS TABLE ("ret" int4) language sql as $$ SELECT ((($1 :: int4) * ($2 :: int4)) + "col") AS "ret" FROM "tab" AS "tab" $$;

createOrReplaceSetFunction

Arguments

:: (Has sch db schema, KnownSymbol fun, SListI args, SListI rets)

=> QualifiedAlias sch fun

function alias

-> NP (TypeExpression db) args

arguments

-> NP (Aliased (TypeExpression db)) rets

return type

-> FunctionDefinition db args ('ReturnsTable rets)

function definition

-> Definition db (Alter sch (CreateOrReplace fun ('Function (args :=> 'ReturnsTable rets)) schema) db)

Create or replace a set function.

>>> type Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])
>>> type Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4])
>>> :{
let
  definition :: Definition (Public '["tab" ::: Tab, "fn" ::: Fn]) (Public '["tab" ::: Tab, "fn" ::: Fn])
  definition = createOrReplaceSetFunction #fn (int4 *: int4) (int4 `as` #ret) $
    languageSqlQuery (select_ ((param @1 * param @2 + #col) `as` #ret) (from (table #tab)))
in printSQL definition
:}
CREATE OR REPLACE FUNCTION "fn" (int4, int4) RETURNS TABLE ("ret" int4) language sql as $$ SELECT ((($1 :: int4) * ($2 :: int4)) + "col") AS "ret" FROM "tab" AS "tab" $$;

Drop

dropFunction

Arguments

:: (Has sch db schema, KnownSymbol fun)

=> QualifiedAlias sch fun

function alias

-> Definition db (Alter sch (DropSchemum fun 'Function schema) db)

Drop a function.

>>> type Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4))
>>> :{
let
  definition :: Definition (Public '["fn" ::: Fn]) (Public '[])
  definition = dropFunction #fn
in printSQL definition
:}
DROP FUNCTION "fn";

dropFunctionIfExists

Arguments

:: (Has sch db schema, KnownSymbol fun)

=> QualifiedAlias sch fun

function alias

-> Definition db (Alter sch (DropSchemumIfExists fun 'Function schema) db)

Drop a function.

>>> type Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4))
>>> :{
let
  definition :: Definition (Public '[]) (Public '[])
  definition = dropFunctionIfExists #fn
in printSQL definition
:}
DROP FUNCTION IF EXISTS "fn";

Function Definition

newtype FunctionDefinition db args ret

Body of a user defined function

Constructors

UnsafeFunctionDefinition

Fields

• renderFunctionDefinition :: ByteString

Instances

Eq (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

Methods

(==) :: FunctionDefinition db args ret -> FunctionDefinition db args ret -> Bool

(/=) :: FunctionDefinition db args ret -> FunctionDefinition db args ret -> Bool

Show (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

Methods

showsPrec :: Int -> FunctionDefinition db args ret -> ShowS

show :: FunctionDefinition db args ret -> String

showList :: [FunctionDefinition db args ret] -> ShowS

Generic (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

Associated Types

type Rep (FunctionDefinition db args ret) :: Type -> Type

Methods

from :: FunctionDefinition db args ret -> Rep (FunctionDefinition db args ret) x

to :: Rep (FunctionDefinition db args ret) x -> FunctionDefinition db args ret

NFData (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

Methods

rnf :: FunctionDefinition db args ret -> ()

RenderSQL (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

Methods

renderSQL :: FunctionDefinition db args ret -> ByteString

type Rep (FunctionDefinition db args ret)

Defined in Squeal.PostgreSQL.Definition.Function

type Rep (FunctionDefinition db args ret) = D1 ('MetaData "FunctionDefinition" "Squeal.PostgreSQL.Definition.Function" "squeal-postgresql-0.8.0.0-HHFIvalMWy63oPSAK2xG8g" 'True) (C1 ('MetaCons "UnsafeFunctionDefinition" 'PrefixI 'True) (S1 ('MetaSel ('Just "renderFunctionDefinition") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 ByteString)))

languageSqlExpr

Arguments

:: Expression 'Ungrouped '[] '[] db args '[] ret

function body

-> FunctionDefinition db args ('Returns ret)

Use a parameterized Expression as a function body

languageSqlQuery

Arguments

:: Query '[] '[] db args rets

function body

-> FunctionDefinition db args ('ReturnsTable rets)

Use a parametrized Query as a function body