At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According to the postgresql docs I should be able to remove a key from an hstore with the following operator

As per the postgres docs hstore - text delete key from left operand 'a=>1, b=>2, c=>3'::hstore - 'b'::text

In my code I have

IF item.details ? 'vin' THEN item.details::hstore - 'vin'::TEXT; END IF;

When I go to create the function I get the syntax error

'syntax error at or near "::"'

When I remove the type declarations from the end of the values I get the error

'syntax error at or near "-"'

As far as I can see this conforms to how the postgres docs say the operator should work at http://www.postgresql.org/docs/9.3/static/hstore.html . Is there something I am missing?

up vote 4 down vote accepted

You're trying to use the expression item.details::hstore - 'vin'::TEXT as a PL/PgSQL statement.

Where's the result supposed to go?

At a guess, I think what you intended to write is:

item.details := item.details - 'vin'::TEXT;

i.e. "set item.details to the old value of item.details with the key vin removed".

It's kind of hard to be sure without more context.

  • That is exactly what I meant, and the fix worked. Thank you. – jacobsoloid Oct 6 '14 at 13:55

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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