User: Password:
|
|
Subscribe / Log in / New account

HStore syntax

HStore syntax

Posted Sep 21, 2010 20:31 UTC (Tue) by epa (subscriber, #39769)
In reply to: PostgreSQL 9.0 arrives with many new features by flewellyn
Parent article: PostgreSQL 9.0 arrives with many new features

The article gives an example of deleting a key:
SELECT profile - 'Occupation' FROM user_profile WHERE user_id = 5;
Is this right? You can mutate the data with a SELECT statement?


(Log in to post comments)

HStore syntax

Posted Sep 21, 2010 20:59 UTC (Tue) by andresfreund (subscriber, #69562) [Link]

It doesn't delete any stored value - the value returned by the select is modified.
If you wanted to modify the stored value you would need an UPDATE...

HStore syntax

Posted Sep 21, 2010 21:29 UTC (Tue) by dskoll (subscriber, #1630) [Link]

Josh's example reads Or even delete specific keys:

I assume he means: Or even delete specific keys from the return value of SELECT. At least I hope that's what it means...

HStore syntax

Posted Sep 21, 2010 21:38 UTC (Tue) by jberkus (subscriber, #55561) [Link]

Actually, that was a paste-o which neither I nor my proofreader caught. The example, of course, should have been:

UPDATE user_profile SET profile = profile - 'Occupation'
WHERE user_id = 5;

HStore syntax

Posted Sep 30, 2010 23:57 UTC (Thu) by MattPerry (guest, #46341) [Link]

Can the SELECT statement be removed from the article? Having it there, lined through or not, is still very confusing.

HStore syntax

Posted Oct 1, 2010 3:16 UTC (Fri) by jake (editor, #205) [Link]

> Having it there, lined through or not, is still very confusing.

The problem is that folks reading the comments will get confused as well, which is why I left it in but with strike-through. Is it really that unclear that it is a fix?

jake

HStore syntax

Posted Oct 1, 2010 18:42 UTC (Fri) by MattPerry (guest, #46341) [Link]

> Is it really that unclear that it is a fix?

Yes, it was. I had no idea what the strike-through was supposed to signify so I ignored it and then spent time wondering why a select and then update would be needed.

> The problem is that folks reading the comments will get confused as well

A better way to handle that is to just reply to the comments in question and mention that you have fixed the problem. Most people will read the article before the comments and struck-through text provides information with no value.

Mutating with SELECT

Posted Sep 28, 2010 1:06 UTC (Tue) by ringerc (subscriber, #3071) [Link]

While that specific example was an error in the article, yes, it is possible to mutate data with SELECT. SELECT may invoke stored functions - written in C, SQL, PL/PgSQL, PL/Perl, etc - that mutate data. SQL and PL/PgSQL functions invoked from SELECT may use any SQL, including INSERT/UPDATE/DELETE but also DDL. Other languages may use the SPI to affect the database's state and contents.


Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds