0

I have a composite type as below:

CREATE TYPE send_email_input AS (
    receiver_email text,
    template_id text,
    template_name text,
    subject text,
    template_content jsonb);

I have a function as below that takes in the above composite type as the input parameter:

CREATE OR REPLACE FUNCTION send_email(email_input send_email_input)
 RETURNS send_email_result
 LANGUAGE plpython3u
 SECURITY DEFINER
 SET search_path TO 'dcp_extensions'
AS $function$
//do something
$function$

Now when I try to run :\

select send_email(cast(row('alan.francis@xyz.com', 
                                                                            'reset-password', 
                                                                            'xyz-email', 
                                                                            'test',        
                                                                            ('{"authnURL": "example.com","toFirstName":"ALan"}' ::TEXT)::jsonb
        
    ) as send_email_input));

This seems to work fine. But when I try to execute it inside of a format function like below:

select send_email(cast((
SELECT FORMAT('row(''%s'', ''reset-password'',
''xyz-email'', ''Signup Successful'',
(''{"authnURL": "%s","toFirstName":"%s"}'' ::TEXT)::jsonb)',
'alan.francis@xyz.com',
'example.com','Alan')
)as send_email_input));

I get the error below:

`SQL Error [22P02]: ERROR: malformed record literal: "row('alan.francis@xyz.com', 'reset-password',
'xyz-email', 'Signup Successful',
('{"authnURL": "example.com","toFirstName":"Alan"}' ::TEXT)::jsonb)"
  Detail: Missing left parenthesis.`

Please let me know what is going wrong with this syntax.
  • I would suggest using [dollar quoting](https://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql) for your `format` template string. This would make things a lot more clear and you will see what's missing and where. – Stefanov.sm Aug 13 '21 at 11:01
  • There is one extra closing bracket after `as send_email_input` – Stefanov.sm Aug 13 '21 at 11:12

0 Answers0