haskell-beam/beam

How to use Postgres Functions?

Open

#758 opened on Apr 19, 2025

 (2 comments) (0 reactions) (0 assignees)Haskell (191 forks)auto 404
docsgood first issuehelp wanted

Repository metrics

Stars
 (631 stars)
PR merge metrics
 (PR metrics pending)

Description

Hi there, I'm trying to call a PostgreSQL function such as the following from within my haskell code, with no success:

CREATE FUNCTION my_fancy_function
       (arg1 integer,
        arg2 character varying) 
RETURNS character varying
LANGUAGE plpgsql
AS $$
BEGIN 
    RETURN "Success"
END
$$;

Initially, I tried using customExpr_ and got this error when attempting to make my own SELECT my_fancy_function(1, 'something'); via a function with (Monoid a, IsString a) as in the docs:

SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"SELECT\"", sqlErrorDetail = "", sqlErrorHint = ""}

Later on I discovered that there is functionCallE, but I could not get it to work. My first attempt was something like this (this does not type check):

functionCall_ ::
  (Projectible be a, BeamSqlBackend be, IsSql99ExpressionSyntax syntax) =>
  Text ->
  [QExpr syntax s a] ->
  QExpr syntax s t
functionCall_ functionName args = QExpr (const $ functionCallE (functionNameE functionName) args)

Further, assuming functionCall_ can be made, would we use it via the function select? I noticed that we don't have SqlExpression as we do for SqlSelect, SqlInsert, and SqlUpdate. Thanks in advance.

Contributor guide