Hello again:
When data comes from a JSON column everything is OK, but when it comes from
the json functions in Postgres something goes wrong for me.
I have extracted the subquery from my first question to investigate it more
clearly in an isolated Lite app (Mojolicious Lite is soooo great…)
This app creates a table and fills it with rows. Some rows has accented
vocals.
- When I query the unaccented rows ('WHERE is_accented = 0') it renders
OK.
- When I query the accented rows ('WHERE is_accented = 1') it fails
with a UTF problem
The original idea comes from this blog:
http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql
Here is the Lite app:
#!/usr/bin/env perl
use Mojolicious::Lite;
use Mojo::Pg;
get '/' => sub {
my $c = shift;
# **************************************************
# SET UP DATABASE mojotest_pg
# **************************************************
my $pg = Mojo::Pg->new('postgresql://postgres@/mojotest_pg');
my $db = $pg->db;
# Create a table
$db->query('create table if not exists review (review_id SERIAL PRIMARY
KEY, is_accented integer, comment text )');
# Check the records count
my $count = $db->query( 'SELECT COUNT(*) FROM review')->hash->{count};
# If no rows found, insert some rows of data, with and without accents
unless ($count) {
my $insert = 'INSERT INTO review (is_accented, comment) VALUES (?,
?)';
foreach my $ii (1..2) {
$db->query($insert, 0, "aeiou$ii");
$db->query($insert, 1, "áéíóú$ii"); # The accented has 1 in the
is_accented column
}
}
# **************************************************
# TESTING
# **************************************************
# To get rows WITH accents use 'WHERE is_accented = 0'
# To get rows WITHOUT accents use 'WHERE is_accented = 1'
my $query = "SELECT array_to_json(array_agg(row_to_json(a))) as
expected_json
FROM (
SELECT *
FROM review
)a
WHERE is_accented = 0
";
my $reviews = $db->query( $query )->expand->hash;
$c->render(json => $reviews->{expected_json});
};
app->start;
Any suggestion will be appreciated, even a "don´t follow this approach
because…"
Regards:
Nacho B.
On Saturday, February 14, 2015 at 12:45:15 AM UTC+1, sri wrote:
>
> Any ideas? What should I try?
>>
>
> Well, this passes just fine for me.
>
> $pg->db->query('select ?::jsonb as foo', {json => {spanish =>
> 'áéíñ'}})->expand->hash;
>
> --
> sebastian
>
--
You received this message because you are subscribed to the Google Groups
"Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.
