squeal-postgresql-0.9.0.0: Squeal PostgreSQL Library
Copyright(c) Eitan Chatav 2019
Maintainereitan@morphism.tech
Stabilityexperimental
Safe HaskellNone
LanguageHaskell2010

Squeal.PostgreSQL.Definition.Function

Description

create and drop functions

Synopsis

Create

createFunction Source #

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 Source #

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 Source #

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 Source #

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 Source #

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 Source #

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 Source #

Body of a user defined function

Instances

Instances details
Eq (FunctionDefinition db args ret) Source # 
Instance details

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) Source # 
Instance details

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) Source # 
Instance details

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) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition.Function

Methods

rnf :: FunctionDefinition db args ret -> () #

RenderSQL (FunctionDefinition db args ret) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition.Function

type Rep (FunctionDefinition db args ret) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition.Function

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

languageSqlExpr Source #

Arguments

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

function body

-> FunctionDefinition db args ('Returns ret) 

Use a parameterized Expression as a function body

languageSqlQuery Source #

Arguments

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

function body

-> FunctionDefinition db args ('ReturnsTable rets) 

Use a parametrized Query as a function body