Обсуждение: Appending a newline to a column value - in a psql cronjob
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
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: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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=' ||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=' ||id|| ' ' ..... "; I think the one you're groping in the dark for is this: psql -c "select 'http://mysite/user.php?id=' ||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=' ||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