Hi,

I have a UDT:

CREATE TYPE foo AS (a integer, b integer, c hstore);

I need to construct the literal representation of an array of these
for input to a textual COPY. The hstore escaping rules are fairly
clear (I can quote all keys and values with double quotes, and escape
double quotes in keys and values with a backslash (two backslahses,
actually, due to standard_conforming_strings being off). Putting this
into my UDT literal is also fairly clear (I quote the full hstore
literal with double quotes, I double the double quotes around hstore
keys and values, and add four more backslashes (one for the hstore
literal, one for the UDT literal, and double that for
standard_conforming_strings). So each quote in an hstore key or value
is preceded by six backslashes (and each backslash is preceded by
seven). However, this is where I get lost. I tried following both the
array docs and trying to reverse engineer the behavior by using the
ARRAY[...] syntax, but I can't figure out the logic. Any suggestions
(other than to run screaming from this affront to proper relational
design--I have my reasons for it)?

Thanks,
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

Search Discussions

  • Tom Lane at Sep 22, 2010 at 7:58 pm

    Maciek Sakrejda writes:
    I have a UDT:
    CREATE TYPE foo AS (a integer, b integer, c hstore);
    I need to construct the literal representation of an array of these
    for input to a textual COPY. The hstore escaping rules are fairly
    clear (I can quote all keys and values with double quotes, and escape
    double quotes in keys and values with a backslash (two backslahses,
    actually, due to standard_conforming_strings being off). Putting this
    into my UDT literal is also fairly clear (I quote the full hstore
    literal with double quotes, I double the double quotes around hstore
    keys and values, and add four more backslashes (one for the hstore
    literal, one for the UDT literal, and double that for
    standard_conforming_strings). So each quote in an hstore key or value
    is preceded by six backslashes (and each backslash is preceded by
    seven). However, this is where I get lost.
    On the whole I think you'd be best off doing that in three passes.
    First, run the appropriate escaping rules on each hstore key and
    value string, and actually assemble a valid input string for hstore.
    Then, run the value escaping rules for records on that string, and
    assemble it together with the other field values into a valid input
    string for a record type. Finally, run the array escaping rules
    on that string, and assemble it together with others into a valid
    array input string.

    The record and array escaping rules are here:
    http://www.postgresql.org/docs/9.0/static/rowtypes.html#AEN7091
    http://www.postgresql.org/docs/9.0/static/arrays.html#ARRAYS-IO
    and I think the hstore rules are spelled out in the docs for that
    contrib module.
    I tried following both the
    array docs and trying to reverse engineer the behavior by using the
    ARRAY[...] syntax, but I can't figure out the logic.
    Neither record nor hstore quote exactly like arrays, so that's probably
    part of your problem. (No doubt it'd be better if they did, but we've
    got too much historical baggage :-()

    regards, tom lane
  • Maciek Sakrejda at Sep 22, 2010 at 11:11 pm

    The record and array escaping rules are here:
    http://www.postgresql.org/docs/9.0/static/rowtypes.html#AEN7091
    http://www.postgresql.org/docs/9.0/static/arrays.html#ARRAYS-IO
    and I think the hstore rules are spelled out in the docs for that
    contrib module.
    Thanks, that helped a lot. I think I was going off of what I read in
    Section 8.15.2, but 8.15.5 is much more helpful. Should have kept
    reading. And I just realized I'd been doubling backslashes too many
    times for standard_conforming_strings, which certainly didn't help
    things.
    Neither record nor hstore quote exactly like arrays, so that's probably
    part of your problem. (No doubt it'd be better if they did, but we've
    got too much historical baggage :-()
    I understand completely.

    Thanks for your help,
    ---
    Maciek Sakrejda | System Architect | Truviso

    1065 E. Hillsdale Blvd., Suite 215
    Foster City, CA 94404
    (650) 242-3500 Main
    www.truviso.com
  • Maciek Sakrejda at Sep 24, 2010 at 1:24 am
    Ok, so, stupid question: how does all this interact with COPY escaping
    rules[1]?:

    Backslash characters (\) can be used in the COPY data to quote
    data characters that might otherwise be taken as row or column
    delimiters. In particular, the following characters must be preceded
    by a backslash if they appear as part of a column value: backslash
    itself, newline, carriage return, and the current delimiter character.


    I imagined that this would be just another layer, but in my initial
    tests, I was not doing this, and things worked. When I add it in, I
    get too many levels of escaping. I can see this with even something
    fairly simple:

    postgres=# select version();
    version
    -----------------------------------------------------------------------------------------------------------
    PostgreSQL 8.4.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
    (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
    (1 row)

    postgres=# create type foo as (a text);
    CREATE TYPE
    postgres=# create table bar(a foo[]);
    CREATE TABLE
    postgres=# copy bar from stdin;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    {"(\\\\"baz\\\\")}
    \.
    ERROR: malformed array literal: "{"(\\"baz\\")}"
    CONTEXT: COPY bar, line 1, column a: "{"(\\"baz\\")}"
    postgres=# show standard_conforming_strings;
    standard_conforming_strings
    -----------------------------
    off
    (1 row)

    My escaping logic for the above COPY input: "baz" is double-quoted as
    a value in a UDT. When I put that UDT in an array literal, I enclose
    it in double quotes and I precede all double quote characters with a
    backslash. Because of standard_conforming_strings being off, I escape
    all backslashes with backslashes (so I now have two backslashes and a
    quote). Because of text-mode COPY, I escape each of those again (so I
    now have four backslashes and a quote).

    I seem to be missing something. Does standard_conforming_strings not
    apply during COPY? Or is there something else I'm missing?

    Thanks,

    [1]: http://www.postgresql.org/docs/8.3/static/sql-copy.html
    ---
    Maciek Sakrejda | System Architect | Truviso

    1065 E. Hillsdale Blvd., Suite 215
    Foster City, CA 94404
    www.truviso.com
  • Tom Lane at Sep 24, 2010 at 4:01 am

    Maciek Sakrejda writes:
    I seem to be missing something. Does standard_conforming_strings not
    apply during COPY?
    It does not. That setting is about literal strings in SQL commands.
    The COPY escaping rules do not depend on it.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 22, '10 at 5:43p
activeSep 24, '10 at 4:01a
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Maciek Sakrejda: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase