Parameter defaults for ALTER FUNCTION?

Question

I'm trying to migrate one of my oracle databases to Postgres 9.5. I`m currently trying to create the next function:

-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:oralab

SET client_encoding TO 'UTF8';
SET search_path = repdev,public,oracle,utl_file,extensions;
show search_path;

CREATE OR REPLACE FUNCTION Bl_Create_Flat_File_Pg.set_charset (file_id 
utl_file.file_type, p_title text DEFAULT NULL) RETURNS VOID AS $body$
BEGIN
.....
End;
--
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
STABLE;
ALTER FUNCTION Bl_Create_Flat_File_Pg.set_charset (file_id 
utl_file.file_type, p_title text DEFAULT NOT NULL ) OWNER TO repdev;

but when I`m running it in psql iget the next error:

ERROR:  syntax error at or near "DEFAULT"
LINE 1: ...charset (file_id utl_file.file_type, p_title text DEFAULT NU...

if you don't specify default for the last argument it will be default null - just remove ` DEFAULT NULL` - it is so already in your case — Vao Tsun, Jul 05 '17 at 10:35
There is no obvious syntax error in the provided statement: [test](http://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=91800e0859cc8110c5f2d2169e90ced0). Is it just single statement, or it is a part of some bigger script? — Abelisto, Jul 05 '17 at 11:03
That is not true - without `DEFAULT NULL` you *have* to supply an argument, with it you don't have to. The error is surprising (I would have expected `schema "utl_file" does not exist`). Is this a modified or forked PostgreSQL you are running? — Laurenz Albe, Jul 05 '17 at 11:04
what do you mean by modified or forked ? just downloaded the offical postgresql 9.5 and installed it from souce files.. — JeyJ, Jul 05 '17 at 11:07
Hi, its part of an .sql dump file that I try to import to the db. I didnt include the body but the body includes some UTL_FILES.PUT_LINE and nothing more. — JeyJ, Jul 05 '17 at 11:23

score 5 · Accepted Answer · edited Jul 07 '17 at 14:25

For the ALTER FUNCTION you only need to repeat the parameter definitions (actually only the parameter types are required), not the default clause of a parameter:

ALTER FUNCTION Bl_Create_Flat_File_Pg.set_charset(file_id utl_file.file_type, p_title text) 
OWNER TO repdev;

or shorter:

ALTER FUNCTION Bl_Create_Flat_File_Pg.set_charset(utl_file.file_type, text) 
OWNER TO repdev;

If you compare the documentation of the CREATE FUNCTION statement, which is:

CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )

to the ALTER FUNCTION documentation:

ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )

you will notice that ALTER FUNCTION does not allow the DEFAULT clause.

If the code you have shown us really was generated by Ora2Pg then you should report that as an error to the Ora2Pg authors.

Thanks ! yes, it was generated by Ora2PG. I will report this bug. — JeyJ, Jul 05 '17 at 12:41
@Mariel: The key word here is "function signature" and there are utility functions which the Ora2P guys *should* be using, particularly: `pg_get_function_identity_arguments(func_oid)` More: https://stackoverflow.com/a/12127714/939860 — Erwin Brandstetter, Jul 05 '17 at 18:14

Parameter defaults for ALTER FUNCTION?

1 Answers1