0

I have following code for creating gist index

CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx 
    ON web_stats_project_107 USING gist (properties) 
    WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore  
        AND properties @> '"block_level_1"=>"head"'::hstore 
        AND properties @> '"block_level_2"=>"cross-news"'::hstore
        AND properties ? 'block_level_5'::text
        AND (NOT (properties ? 'block_level_6'::text));

It works smoothly when I use it directly.

But I need to use it inside the trigger with some variables. And here the problem comes.

I tried two variants of code:

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist 
ON public.'||Fulltable_name||' USING gist (properties) 
WHERE properties @> '"block_level_0"=>"cerber-head"'::hstore 
AND ...;

and

createIndex := 'CREATE INDEX idx_'||Fulltable_name||'_properties_crosspromo_gist 
ON public.'||Fulltable_name||' USING gist (properties) 
WHERE (((((properties @> '"block_level_0"=>"cerber-head"'::hstore) 
AND ...;

And I even can't apply it for DB because of syntax error

ERROR: syntax error at or near "=>" LINE 32: ...perties) WHERE (((((properties @> '"block_level_0"=>"cerber-...

or

ERROR: syntax error at or near "CREATE" LINE 32:
createIndex := 'createIndex := 'CREATE INDEX idx...

Seems I have to escape some symbols with some characters.

Any ideas how to do that?

closed as off-topic by dezso, mustaccio, Philᵀᴹ, Max Vernon, Shanky Feb 5 '16 at 9:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, mustaccio, Philᵀᴹ, Max Vernon, Shanky
If this question can be reworded to fit the rules in the help center, please edit the question.

1
createIndex := 'CREATE INDEX idx_'
    || Fulltable_name
    || '_properties_crosspr_gist ON public.'
    || Fulltable_name
    || ' 
    USING gist (properties) 
    WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore 
      AND properties @> $$"block_level_1"=>"head"$$::hstore 
      AND properties @> $$"block_level_2"=>"cross-news"$$::hstore 
      AND properties ? $$block_level_5$$::text 
      AND NOT properties ? $$block_level_6$$::text';
  • Please check what the format() function does and use it. Gives a much cleaner feeling. – dezso Feb 4 '16 at 11:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.