[SQL] SQL Statement Missing From Log
Dear Helpful People, I'm getting a bunch of: 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "SET" at character 9 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "fetch" at character 9 2008-10-25 14:36:59 PDT ERROR: current transaction is aborted, commands ignored until end of transaction block With no idea where the "SET" is coming from. My logging configuration is as follows. What am I doing wrong? How can I get the full failing SQL statement? # grep "log_" postgresql.conf | grep -v "^#" log_min_messages = notice # =notice Values, in order of decreasing detail: log_error_verbosity = verbose # =default terse, default, or verbose messages log_min_duration_statement = 250# -1 is disabled, 0 logs all statements log_line_prefix = '%t ' # Special values: debug_pretty_print =on # = off -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL Statement Missing From Log
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > I'm getting a bunch of: > 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "SET" at character 9 > 2008-10-25 14:36:59 PDT ERROR: syntax error at or near "fetch" at > character 9 > 2008-10-25 14:36:59 PDT ERROR: current transaction is aborted, commands > ignored until end of transaction block > With no idea where the "SET" is coming from. My logging configuration > is as follows. What am I doing wrong? How can I get the full failing > SQL statement? log_min_error_statement needs to be ERROR or less. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to hand over array as variable in plpgsql function?
Dear list, I am trying to hand over an float4 array of a previous query in my plpgsql function. The query returns the correct array but when I try to hand over this array to another function I get the error message: ERROR: operator is not unique: "unknown" || real[] SQL state: 42725 Hint: Could not choose a best candidate operator. You may need to add explicit type casts. Context: PL/pgSQL function "get_matching_ids_sql_id" line 19 at execute statement. The function looks like this: DECLARE id_result integer; b float4[]; BEGIN EXECUTE 'SELECT precip_control FROM precip_arrays WHERE id = 400' INTO b; [...] EXECUTE 'SELECT id FROM precip_arrays WHERE '||b||' = precip_control;' INTO id_result; RETURN id_result ; END; I know that there is a problem with the " '||b||' " part, so could someone point me in the right direction how to right this statement correctly, so that the array can be processed by this SQL statement? Thanks in advance and kind regards Jan Peters-Anders -- "Feel free" - 5 GB Mailbox, 50 FreeSMS/Monat ... Jetzt GMX ProMail testen: http://www.gmx.net/de/go/promail -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: [SQL] grouping/clustering query
I forgot the list.
-- Forwarded message --
From: Osvaldo Kussama <[EMAIL PROTECTED]>
Date: Mon, 27 Oct 2008 12:28:57 -0200
Subject: Re: [SQL] grouping/clustering query
To: David Garamond <[EMAIL PROTECTED]>
2008/10/24, David Garamond <[EMAIL PROTECTED]>:
> Tony, Joe, Steve,
>
> Thanks for the follow-ups. Yes, the problem is related to double-entry
> accounting, where one needs to balance total debit and credit
> (payments and invoices) in each journal/transaction.
>
> Due to time constraint, I ended up doing this in the client-side
> programming language, since I am nowhere near fluent in PLs. The
> algorithm should be simple (at least the "brute force" version), it's
> basically checking if each element of the pair (txid, invoiceid) is
> already mentioned in some journal and if it is, add the pair to the
> journal, otherwise create a new journal with that pair as the first
> entry. I believe this can easily be implemented in a PL. But still I
> wonder if there is some SQL incantation that can do the same without
> any PL.
>
Interesting problem.
I think there are no SQL-only solution.
Using arrays and PL/pgSQL function:
bdteste=# SELECT * FROM bar;
aid | bid
-+-
A | 1
A | 3
B | 1
B | 2
C | 5
D | 6
D | 7
E | 8
F | 8
(9 registros)
bdteste=# CREATE OR REPLACE FUNCTION combina() RETURNS setof record AS $$
bdteste$# DECLARE
bdteste$#res record;
bdteste$#res1 record;
bdteste$# BEGIN
bdteste$#CREATE TEMP TABLE foobar(
bdteste$# fbaid text[],
bdteste$# fbbid int[])
bdteste$#ON COMMIT DROP;
bdteste$#
bdteste$#FOR res IN SELECT agr1, bid FROM (SELECT bid,
array_accum(aid) AS agr1 FROM bar
bdteste$# GROUP BY bid) b1 ORDER BY array_upper(agr1,
1) DESC, agr1 LOOP
bdteste$# SELECT * INTO res1 FROM foobar WHERE fbaid @> res.agr1;
bdteste$# IF NOT FOUND THEN
bdteste$# INSERT INTO foobar VALUES (res.agr1, array[res.bid]);
bdteste$# ELSE
bdteste$# UPDATE foobar SET fbbid = array_append(fbbid,
res.bid) WHERE fbaid @> res.agr1;
bdteste$# END IF;
bdteste$#END LOOP;
bdteste$#
bdteste$#RETURN QUERY SELECT * FROM foobar;
bdteste$# END;
bdteste$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
bdteste=#
bdteste=# SELECT * FROM combina() AS(a text[], b int[]);
a |b
---+-
{E,F} | {8}
{A,B} | {1,3,2}
{C} | {5}
{D} | {7,6}
(4 registros)
Osvaldo
PS. - Aggregate array_accum defined at:
http://www.postgresql.org/docs/current/interactive/xaggr.html
- If you need sorted arrays use Andreas Kretschmer's function array_sort:
http://archives.postgresql.org/pgsql-general/2007-02/msg01534.php
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to hand over array as variable in plpgsql function?
"Jan Peters" <[EMAIL PROTECTED]> writes: > ERROR: operator is not unique: "unknown" || real[] > EXECUTE 'SELECT id FROM precip_arrays WHERE '||b||' = precip_control;' INTO > id_result; Seems like using EXECUTE is the hardest possible way to do this. Why don't you just SELECT? SELECT id FROM precip_arrays WHERE b = precip_control INTO id_result; If you insist on using EXECUTE then you're going to have to fool with converting the array to an appropriate text representation. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
