Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re^4: What defines the output format of a Postgres Timestamp

by Skeeve (Parson)
on Jan 27, 2021 at 07:53 UTC ( #11127510=note: print w/replies, xml ) Need Help??


in reply to Re^3: What defines the output format of a Postgres Timestamp
in thread What defines the output format of a Postgres Timestamp

data returned from the DB matches the order your program expects

My program does not expect any order. That's what "hashes" are made for.

# Pseudocode my $result = $db->query('SELECT * FROM my_table')->hashes; print "First value of price was: ",$result->[0]{price},"\n";

s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Replies are listed 'Best First'.
Re^5: What defines the output format of a Postgres Timestamp
by haukex (Archbishop) on Jan 27, 2021 at 14:10 UTC
    My program does not expect any order. That's what "hashes" are made for.

    Well, to continue down this avenue of argumentation: If you're using hashes, then isn't it also likely that you know all the names of the keys in advance? Then you could do $db->select( my_table => ['price',...] )->hashes. Then you wouldn't be getting more columns than necessary when your table definition changes, you'd get hard failures instead of unexplained undefs when your column names change, and so on. I know that listing all columns feels tedious, but I tend to agree that SELECT * is brittle, and IMHO one solution is having the column names in Perl variables and then building the queries instead of hard-coding the SQL.

    Plus, query building with SQL::Abstract means you can do fun things on the Perl side like using map to easily apply the TO_CHAR function to multiple columns, something like $db->select( my_table => [ map { \["TO_CHAR(?,'YYYY-MM-DDTHH24:MI:SS') AS ?",$_,$_] } @datetime_columns ] ) (untested).

    Yet another idea might be to look at extending Mojo::Pg::Results's expand method, which already processes JSON columns, you could use this to convert date/time columns to DateTime objects automatically. The "advantage" of this solution would be that you could continue using SELECT *...

      Okay¬Ö Let's continue.

      Your arguments are valid. You also gave me some ideas about SQL::Abstract.

      My idea was that I have a few levels where the data is handled. When the need arises to add a column, I need not change the code which retrieves the data from the database, because it will, thanks to the * give me all columns. And this might not be just one function, but several.

      I only need to change those functions, which have to handle the new column.


      s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
      +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
        I only need to change those functions, which have to handle the new column.

        Sure, though I think you'd still have a problem if a column gets renamed? But don't get me wrong, I'm not fixated on this argument against SELECT * - you know your database better than I do, so it may be possible for you to say now that columns will never get renamed and adding columns will be rare, even in the future. I just have been enjoying writing code using SQL::Abstract via Mojo::Pg, and I see several advantages over hard-coded SQL (there are of course disadvantages too, like slightly more verbose code in certain cases, and I assume the performance is not as good when compared to hard-coded SQL). I do still have a few hard-coded SQL statements in my codebase too.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11127510]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2022-12-09 17:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?