Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21

    Unanswered: selects and quotation marks..?!

    hi,

    I seem to have a '' problem...?!
    I am trying to execute the following statement in a function (to be used in trigger) where strTab

    DECLARE
    referrer_keys2 record;
    strTab,strQ varchar;
    ...

    FOR referrer_keys2 IN SELECT OBJID1 FROM '' || strTab || '' where qualitaet = '' || strQ || '' LOOP
    RAISE NOTICE '' inside......'';
    END LOOP;

    but I get an error when I actually run the trigger :
    parser: parse error at or near "' || strTab '"

    surely irs because I'm using the '' wrong, but how then?

    thanks

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    don't see it

    Is this a double quote or 2 single quotes?

    2 single quotes are right
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21

    2 single quotes

    Its 2 single quotes..but does not work. ' ' || var || ' '

    keeps giving me error time after time. As soon as I put a table name instead of the variable..it works.!?

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    variables

    maybe the variables are empty???
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21
    nope, not empty, just tested again with a raise notice printing the value.

    and another interestibg thing, if i try to do a compare of a number,

    ex.:
    FOR referrer_keys3 IN SELECT OBJID2 FROM tblAll
    where qualitaet = ''|| QUALITAETNR ||'' LOOP

    where qualitaetnr is a number, I get the following error:

    Bad numeric input format '|| QUALITAETNR ||'

    Why? If I put jsut one ' || bla || ' I also get an error..?

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    log

    run the trigger and look into the postgres log what the server sends to the executor!!!

    maybe the variables are interpreted like strings
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21
    you mean the postmaster.log file in cygwin\var\log..?

    nothing interesting there..just the errors mentioned above...

  8. #8
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    log

    you can set the debug level a little bit higher in the postgresql.conf

    do you see alle sql-statements in the log???
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  9. #9
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21
    no, i do not see the sql statements. I set the debug_... oprtions to true, and shutdown the server and restarted it, but not much changed in log.

  10. #10
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    delay

    sorry for the delay

    I don't know exactly what is wrong the pl/pgsql section knowledge is not the best

    but try the usenet
    pgsql-sql this is a good place for this to ask
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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