Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: Wierd pgplsql problem

    hi everyone,

    i am stuck in a very weird problem, it looks as if it is impossible.

    i have written a function in pgplsql which creates a view in the database. It accepts a parameter of type Varchar. Here is the function definition:

    DECLARE
    SQL VARCHAR;
    BEGIN

    SQL = 'CREATE OR REPLACE VIEW Reports.vuReport AS
    SELECT *
    FROM Reports.plan
    WHERE plan_date = ' || $1;

    EXECUTE SQL;
    RETURN 'Testing';

    END;

    The name of the function is: tmp and I execute the function like
    this: select tmp('2006-11-14');
    Now the view is created with the following syntax:
    CREATE OR REPLACE VIEW Reports.vuReport AS
    SELECT *
    FROM Reports.plan
    WHERE plan_date::text = (2006 - 11 - 14)::text;

    This view does not return me correct values as the date is here text, if i had got this view instead, i would have got the correct result:
    CREATE OR REPLACE VIEW Reports.vuReport AS
    SELECT *
    FROM Reports.plan
    WHERE plan_date = '2006-11-14';

    The problem is that, the variable which i am passing does not get quoted. I am not able to find how do i replace 2006-11-14 (without quotes) with '2006-11-14' (with quotes) in my query.

    Kindly Help.
    Thanx & Regards.
    Gaurav

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    There are a couple of ways to do this - with dollar-quoting or multiple quote marks. In PostgreSQL 8.1 manual, look at section 36.2.1. To me, the easiest is to use the quote_literal() method. In your statement, replace $1 with quote_literal($1).

  3. #3
    Join Date
    Dec 2005
    Posts
    2

    Multiple Ways?

    Hi,

    The problem was not solved using quote_literal(). Can you please let me know other ways to do it.

    Thankx n regards.
    Gaurav

  4. #4
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I don't use date formats much, so am not able to offer a lot more help. My suggestion would be to create a string variable, such as stSQL and concatenate the query to the string. Then do a RAISE EXCEPTION and output the string to see what your code is doing. This is a pretty standard way to debug pl/pgsql functions. Once the output string is correct, the problem should be solved. Also, as I mentioned earlier, look at chapter 36 in the manual.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •