Обсуждение: psql and shell scripts

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

which produces this entry in the log file:

query: BEGIN; CREATE TEMP TABLE radacct_archive_temp AS SELECT * FROM
radacct_archive WHERE date_part('month', tstamp) = 06 AND
date_part('year', tstamp) = 2000; UPDATE radacct_archive_temp SET
framedipaddress = NULL WHERE framedipaddress = ''; COPY
radacct_archive_temp TO '/usr/local/pgsql/radius.R73573'; COMMIT;

which shows all of the variables have been properly substituted, leaving
(what appears to me) valid SQL.

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.  If I paste exactly what is in the log into a psql
window, it works as expected.

What am I missing (it has to be simple...)

Thanks!

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

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