|
in reply to Re^2: What defines the output format of a Postgres Timestamp
in thread What defines the output format of a Postgres Timestamp
if you have to replace every "select * …" with an explicit list of all columns
Unless you are introspecting the returned data to determine its structure or otherwise processing it very flexibly, you should be explicitly listing all of the columns you want to ensure that the data returned from the DB matches the order your program expects. "SELECT *" seems to be meant for interactive use and I suspect that the order of columns returned from that type of query is unspecified. |
Re^4: What defines the output format of a Postgres Timestamp
by haukex (Archbishop) on Jan 27, 2021 at 14:08 UTC
|
I suspect that the order of columns returned from that type of query is unspecified.
"Unspecified" sounds a bit like it might change when the database table hasn't changed, and that would surprise me if it were the case. It seems different from vendor to vendor, but at least according to this page on the Postgres Wiki, "Postgres currently defines column order based on the attnum column of the pg_attribute table."
| [reply] |
|
|
The problem is what is considered a table change. What about changing the precision of a column? I'm aware of a (closed source) DB engine which changes the order of columns for some precision changes (namely when the new type takes up more bytes than the old one) but keeps it the same for the rest.
map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
| [reply]
[d/l] |
|
|
The problem is what is considered a table change.
Yes, that's a good point - over in my other replies I've been arguing against SELECT * anyway. I just felt that saying it was "unspecified" was, well, too unspecific :-)
| [reply]
[d/l]
[select] |
Re^4: What defines the output format of a Postgres Timestamp
by Skeeve (Parson) on Jan 27, 2021 at 07:53 UTC
|
# 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
| [reply]
[d/l]
[select] |
|
|
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 *...
| [reply]
[d/l]
[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
| [reply]
[d/l]
[select] |
|
|
|
|
