Appending a newline to a column value - in a psql cronjob

On 01/13/2012 05:11 AM, Alexander Farber wrote:

Hello!

I'm using PostgreSQL 8.4.9 on CentOS 6.2 and with bash.

The following cronjob works well for me
(trying to send a mail to myself - for moderation):

6 6 * * * psql -c "select
'http://mysite/user.php?id=' ||id, about from pref_rep where
length(about)> 1 and last_rated> now() - interval '1 day'"

but I can't figure out how to append a newline to the
1st value (because otherwise the line is too long
and I have to scroll right in my mail reader):

What I've tried sofar:

# history
1001 psql -c "select 'http://mysite/user.php?id=' ||id||'\n', about from .....
1002 psql -c "select 'http://mysite/user.php?id=' ||id||'\\n', about
from .....
1003 psql -c "select 'http://mysite/user.php?id=' ||id|| "\\n",
about from .....
1004 psql -c "select 'http://mysite/user.php?id=' ||id|| \"\\n\",
about from .....
1005 psql -c "select 'http://mysite/user.php?id=' ||id|| \"\n\",
about from .....
1006 psql -c "select 'http://mysite/user.php?id=' ||id|| \'\n\',
about from .....

Thank you
Alex

Given that it's a constant, I would just drop the http header :)

... || id || E'\n' ...

To enable the backslash escape you prefix the literal with the letter E

David J.

On Jan 13, 2012, at 7:11, Alexander Farber <alexander.farber@gmail.com> wrote:

On 2012-01-13, Alexander Farber <alexander.farber@gmail.com> wrote:

Hello!

I'm using PostgreSQL 8.4.9 on CentOS 6.2 and with bash.

The following cronjob works well for me
(trying to send a mail to myself - for moderation):

6 6 * * * psql -c "select
'http://mysite/user.php?id=&#39; ||id, about from pref_rep where
length(about) > 1 and last_rated > now() - interval '1 day'"

but I can't figure out how to append a newline to the
1st value (because otherwise the line is too long
and I have to scroll right in my mail reader):

[several command-line attempts skipped]

I'd be incluned to cheat and use a literal newline like this:

psql -c "select 'http://mysite/user.php?id=&#39; ||id|| '
' ..... ";

I think the one you're groping in the dark for is this:

psql -c "select 'http://mysite/user.php?id=&#39; ||id|| e'\\n' ..... ";

but I think the real problem is that that road doesn't lead where you
want to go as after appending the neline psql reformats the content
into columns (this is usually a good thing).

As you;re using cron and not the command line the rules about what's
allowable change.

try this:

psql -c "select http://mysite/user.php?id=&#39; ||id || e'\n' || about from pref_rep where
length(about) > 1 and last_rated > now() - interval '1 day'"

or possibly with more backslashes: I'm not sure what cron does to backslashes (if anything)

--
⚂⚃ 100% natural