Solved

Escaping special chars in PL/SQL Postgres

Posted on 2004-10-21
2,337 Views
Last Modified: 2012-06-27
How do I escape special chars in PL/SQL in postgres.  I tried to escape the apostrophe into XML escape sequence.  I did something like this and got an error:

vAlias := replace(alias, '''', ''');

The error I got:  ERROR: syntax error at or near "&"



0
Question by:tlchu
    6 Comments
     
    LVL 22

    Expert Comment

    by:earth man2
    PL/SQL does not exist in the postgresql world.  plplsql is wot it is called !

    Apostrophe  living hell can be avoided by using a different format by using for example double dollar $$

    CREATE OR REPLACE FUNCTION test1( varchar ) RETURNS varchar AS $$
    begin
       return replace( $1, '''', '&apos' );
    end;
    $$ LANGUAGE plpgsql;

    select test1('O''Connor');
        test1
    --------------
     O&aposConnor

    If you really need to use a double dollar somewhere in the code you can use some other unique character sequence.
    0
     
    LVL 22

    Expert Comment

    by:earth man2
    CREATE OR REPLACE FUNCTION test1( varchar ) RETURNS varchar AS $$
    begin
       return replace( $1, '''', ''' );
    end;
    $$ LANGUAGE plpgsql;

    as opposed to - well I'm not going to even try to count those apostrophes !

    CREATE OR REPLACE FUNCTION test1( varchar ) RETURNS varchar AS '
    begin
       return replace( $1, '''''''', ''''' );
    end;
    ' LANGUAGE plpgsql;
    0
     
    LVL 22

    Expert Comment

    by:earth man2
    plpgsql is wot it is called
    0
     

    Author Comment

    by:tlchu

    I'd like to know what is the simple syntax for doing apostrophe replacement without the complication of the two functions you've given above.  My original statement:

    vAlias := replace(alias, '''', ''');

    Can it be rewritten in a way that works?  Thanks.
    0
     
    LVL 22

    Expert Comment

    by:earth man2
    I'm not suggesting you encapsulate the replace operation in a function they are just the simplest approach to produce a testable software unit.

    Do you understand the use of $$ to delimit the plpgsql code instead of using apostrophe's ?

    If you use $$

    vAlias := replace(alias, '''', ''');

    otherwise

    vAlias := replace( $1, '''''''', ''''' );
    0
     
    LVL 22

    Accepted Solution

    by:
    otherwise

    vAlias := replace( alias, '''''''', ''''' );
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    splitting into multiple rows oracle 7 212
    Why do I have 2 versions of psql, pg_dump and other tools? 10 1,052
    Don't see the folder 24 288
    Hours Worked 10 75
    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now