Re: psql and shell scripts

Michael Fork <mfork@toledolink.com> writes:
> I am running the following command from a bash script:
> /usr/local/pgsql/bin/psql -c "BEGIN; CREATE TEMP TABLE
> radacct_archive_temp AS SELECT * FROM radacct_archive WHERE
> date_part('month', tstamp) = ${RADACCT_MONTH} AND date_part('year',
> tstamp) = ${RADACCT_YEAR}; UPDATE radacct_archive_te mp SET
> framedipaddress = NULL WHERE framedipaddress = ''; COPY radacct_archive_t
> emp TO '$COPY_RADACCT'; COMMIT;" $MAIN_DB

> However, the script produces the error:

> ERROR:  Relation 'radacct_archive_temp' does not exist

> which doesn't make sense to me, as it is clearly created first, and inside
> of a transaction.

Unfortunately, in 7.0.* and before the whole query string is parsed
before any of it is executed --- and psql sends a -c argument to the
backend as one query.  So radacct_archive_temp doesn't yet exist when
the UPDATE is parsed.

This is fixed in 7.1, but for now you'll need to work around it by doing
something like

    echo "that same query string" | psql $MAIN_DB

which might look like the exact same thing, but in this mode psql breaks
the input at semicolons and sends the commands as separate queries.

            regards, tom lane