Prev: [HACKERS] Built-in connection pool
Next: Issue: Deprecation of the XML2 module'xml_is_well_formed' function
From: Mike Rylander on 28 Jun 2010 11:42
On Mon, Jun 28, 2010 at 11:08 AM, Mike Berrow <mberrow(a)gmail.com> wrote:
> We need to make extensive use of the 'xml_is_well_formed' function provided
> by the XML2 module.
> Yet the documentation says that the xml2 module will be deprecated since
> "XML syntax checking and XPath queries"
> is covered by the XML-related functionality based on the SQL/XML standard in
> the core server from PostgreSQL 8.3 onwards.
> However, the core function XMLPARSE does not provide equivalent
> functionality since when it detects an invalid XML document,
> it throws an error rather than returning a truth value (which is what we
> need and currently have with the 'xml_is_well_formed' function).
> For example:
> select xml_is_well_formed('<br></br2>');
> �xml_is_well_formed
> --------------------
> �f
> (1 row)
> select XMLPARSE( DOCUMENT '<br></br2>' );
> ERROR: �invalid XML document
> DETAIL: �Entity: line 1: parser error : expected '>'
> <br></br2>
> �� � � �^
> Entity: line 1: parser error : Extra content at the end of the document
> <br></br2>
> �� � � �^
> Is there some way to use the new, core XML functionality to simply return a
> truth value
> in the way that we need?.

You could do something like this (untested):

CREATE OR REPLACE FUNCTION my_xml_is_valid ( x TEXT ) RETURNS BOOL AS $$
BEGIN
PERFORM XMLPARSE( DOCUMENT x::XML );
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$ LANGUAGE PLPGSQL;

--
Mike Rylander
| VP, Research and Design
| Equinox Software, Inc. / The Evergreen Experts
| phone: 1-877-OPEN-ILS (673-6457)
| email: miker(a)esilibrary.com
| web: http://www.esilibrary.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 28 Jun 2010 12:07
On Mon, Jun 28, 2010 at 11:42 AM, Mike Rylander <mrylander(a)gmail.com> wrote:
> You could do something like this (untested):
>
> CREATE OR REPLACE FUNCTION my_xml_is_valid ( x TEXT ) RETURNS BOOL AS $$
> BEGIN
> �PERFORM XMLPARSE( DOCUMENT x::XML );
> �RETURN TRUE;
> EXCEPTION WHEN OTHERS THEN
> �RETURN FALSE;
> END;
> $$ LANGUAGE PLPGSQL;

This might perform significantly worse, though: exception handling ain't cheap.

It's not a bad workaround, but I think the OP has a point.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 28 Jun 2010 22:51
On Mon, Jun 28, 2010 at 11:03 AM, Mike Berrow <mberrow(a)gmail.com> wrote:
> Is there some way to use the new, core XML functionality to simply return a
> truth value
> in the way that we need?.

Have you tried using a wrapper function like the one suggested
independently by Mike Rylander and David Fetter upthread? If so,
how'd it work out for you?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Mike Berrow on 28 Jun 2010 23:45
Yes, I went ahead and tried the original suggestion.

Here is what the added function went in as:

CREATE OR REPLACE FUNCTION xml_is_ok(x text)
RETURNS boolean AS
$BODY$
BEGIN
PERFORM XMLPARSE( DOCUMENT x::XML );
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION xml_is_ok(text) OWNER TO postgres;

It worked fine. Thanks Mike and David.

The only other issue is that when I benchmarked it on a 5,000 record data
set that I have,
the original XML2 function ('xml_is_well_formed') took about 9.5 seconds
and this (deprecation driven) replacement took about 17.2 seconds.

-- Mike Berrow

On Mon, Jun 28, 2010 at 7:51 PM, Robert Haas <robertmhaas(a)gmail.com> wrote:

> On Mon, Jun 28, 2010 at 11:03 AM, Mike Berrow <mberrow(a)gmail.com> wrote:
> > Is there some way to use the new, core XML functionality to simply return
> a
> > truth value
> > in the way that we need?.
>
> Have you tried using a wrapper function like the one suggested
> independently by Mike Rylander and David Fetter upthread? If so,
> how'd it work out for you?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
From: Alvaro Herrera on 30 Jun 2010 17:11
Excerpts from David Fetter's message of lun jun 28 12:00:47 -0400 2010:

> While tracking this down, I didn't see a way to get SQLSTATE or the
> corresponding condition name via psql. Is this an oversight? A bug,
> perhaps?

IIRC
\pset VERBOSITY verbose
to get the SQLSTATE.

I don't think you can get the condition name that way, though.

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2
Prev: [HACKERS] Built-in connection pool
Next: Issue: Deprecation of the XML2 module'xml_is_well_formed' function