CODEWITHSUNDEEP
postgresqlplpgsql
Ed Morton
Ed Morton

Reputation: 203532

How can I print text to stdout from a psql query?

I have the following (massaged) psql script invoked from a bash shell:

foo=$( psql -q -t -R $'\x01' -F $'\x02' \
                --variable="title=something" \
                --variable="severity=level9" \
                --pset='format=unaligned' \
                <<'EOF'
SET standard_conforming_strings=on;
SET myvars.title = :title;
SET myvars.severity = :severity;
DO $$
BEGIN
        IF EXISTS ( SELECT 1 from my_database
                WHERE title=current_setting('myvars.title') \
                AND severity=current_setting('myvars.severity') )
        THEN
                RAISE NOTICE 'Found existing entry';
        ELSE
                RAISE NOTICE 'Did not find existing entry';
        END IF;
END;
$$;
EOF
 )

I was hoping to capture in the bash variable "foo" some indication of whether or not the query succeeded. I thought I could add some kind of print/echo/return/whatever statement after each of the RAISE NOTICE statements to output 0 or 1 from the psql statement so it could be captured in foo for later evaluation in the bash script, e.g.:

foo=$( psql -q -t -R $'\x01' -F $'\x02' \
                --variable="title=something" \
                --variable="severity=level9" \
                --pset='format=unaligned' \
                <<'EOF'
SET standard_conforming_strings=on;
SET myvars.title = :title;
SET myvars.severity = :severity;
DO $$
BEGIN
        IF EXISTS ( SELECT 1 from my_database
                WHERE title=current_setting('myvars.title') \
                AND severity=current_setting('myvars.severity') )
        THEN
                RAISE NOTICE 'Found existing entry';
                magical_incantation 1;
        ELSE
                RAISE NOTICE 'Did not find existing entry';
                magical_incantation 0;
        END IF;
END;
$$;
EOF
 )

I've been reading psql documentation for about the past 5 hours and cannot figure out the command/syntax of my imaginary magical_incantation above.

I am new to psql and tweaking someone else's code. I cannot change the main structure of the script, I just need some way to print a value.

Can anyone point me in the right direction?

Upvotes: 3

Views: 11260

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

\echo prints to stdout in psql. I think RAISE NOTICE goes to stderr.

It is possible to select into a psql variable and then echo it. A basic run-down is at How do you use script variables in PostgreSQL?

However I am not sure of any way to pull a query result into a variable at present.

Assuming that is not the answer you are looking for is just a SELECT.

So something like:

SELECT (EXISTS (....))::int;
-- true is 1, false is 0

Note there is no way to do this from a DO statement so you would have to actually create a function if you need to do this from plpgsql.

Upvotes: 5

Related Questions