Thread: Need more info on PL/pgSQL compile listing
I am doing like:
mydb-# \i pxportal2_mod.sql
which gives an error like:
CREATE FUNCTION
psql:pxportal2_mod.sql:309: NOTICE: type reference tx_portal.portal_name%TYPE converted to character varying
psql:pxportal2_mod.sql:309: NOTICE: type reference tx_service_catalog.service_code%TYPE converted to character varying
psql:pxportal2_mod.sql:309: ERROR: syntax error at or near "pxportal2"
LINE 61: pxportal2.dlogerror (v_program_name, v_program_loca...
^
The problem is that there are lots of directives and comments in my input file so that neither line 309 (above) or 61 is actually meaningful.
I would simply like to echo the input lines to the output or get a more verbose message. I spent over an hour googling this but no luck.
This is the beginning of my input file if that helps:
-- Generated by Ora2Pg, the Oracle database Schema converter, version 16.0
-- Copyright 2000-2015 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:LCD1_DEV
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP OFF
-- Oracle package 'PXPORTAL2' declaration, please edit to match PostgreSQL syntax.
-- PostgreSQL does not recognize PACKAGES, using SCHEMA instead.
DROP SCHEMA IF EXISTS pxportal2 CASCADE;
CREATE SCHEMA pxportal2;
Michael Moore <michaeljmoore@gmail.com> writes:
> I am doing like:
> *mydb-# \i pxportal2_mod.sql*
> which gives an error like:
> *CREATE FUNCTION*
> *psql:pxportal2_mod.sql:309: NOTICE: type reference
> tx_portal.portal_name%TYPE converted to character varying*
> *psql:pxportal2_mod.sql:309: NOTICE: type reference
> tx_service_catalog.service_code%TYPE converted to character varying*
> *psql:pxportal2_mod.sql:309: ERROR: syntax error at or near "pxportal2"*
> *LINE 61: pxportal2.dlogerror (v_program_name, v_program_loca...*
> * ^*
> The problem is that there are lots of directives and comments in my input
> file so that neither line 309 (above) or 61 is actually meaningful.
I think 309 will be the file line number of the start of the CREATE
FUNCTION command, while 61 will be the line number within the function
body.
If you have /* ... */ style comments in your file, I think psql might end
up considering the start of the comment before the CREATE FUNCTION command
as being the start point of that command for this purpose.
regards, tom lane
Thanks Tom,
do you know if there is any kind of a switch or command to make the compiler give a more detailed message?
On Mon, Nov 16, 2015 at 4:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Moore <michaeljmoore@gmail.com> writes:
> I am doing like:
> *mydb-# \i pxportal2_mod.sql*
> which gives an error like:
> *CREATE FUNCTION*
> *psql:pxportal2_mod.sql:309: NOTICE: type reference
> tx_portal.portal_name%TYPE converted to character varying*
> *psql:pxportal2_mod.sql:309: NOTICE: type reference
> tx_service_catalog.service_code%TYPE converted to character varying*
> *psql:pxportal2_mod.sql:309: ERROR: syntax error at or near "pxportal2"*
> *LINE 61: pxportal2.dlogerror (v_program_name, v_program_loca...*
> * ^*
> The problem is that there are lots of directives and comments in my input
> file so that neither line 309 (above) or 61 is actually meaningful.
I think 309 will be the file line number of the start of the CREATE
FUNCTION command, while 61 will be the line number within the function
body.
If you have /* ... */ style comments in your file, I think psql might end
up considering the start of the comment before the CREATE FUNCTION command
as being the start point of that command for this purpose.
regards, tom lane