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

Question

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?

score 5 · Accepted Answer · edited May 23 '17 at 12:24

5

\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.

edited May 23 '17 at 12:24

Community

1
1

answered Jun 14 '16 at 06:36

Chris Travers

25,424
6
65
182

I tried `\echo '1';` (with and without the quotes) and got a syntax error at `\ `. I tried adding `2>&1` at the end of the psql command and it didn't capture the RAISE NOTICE output (plus it feels like that can't be the right way to do this). I couldn't figure out how to capture the query result in a variable either but even if I did I still couldn't figure out how to print the variable contents. I've tried various flavors of `SELECT...` with no luck and if I did as you suggest then I don't know how I'd incorporate RAISE NOTICEs. Thanks though. – Ed Morton Jun 14 '16 at 11:35
1

\echo is a *psql* command, not a postgresql command (i.e. front-end, not back-end). Raise notice is not what you want. If you want to do both, define an actual function and select from it. You cannot write to the psql standard output directly from psql so you need something that allows you to select the result, i.e a function. – Chris Travers Jun 14 '16 at 13:38
I just replaced everything from `DO $$` to `$$;` with just the select command `SELECT 1 from my_database WHERE title=current_setting('myvars.title') AND severity=current_setting('myvars.severity')` and it worked, though I had to give up on the RAISE NOTICEs of course. Some day I'll probably have to sit down and really learn this command/language but for now I've got what i need to move on - Thanks for your help. – Ed Morton Jun 14 '16 at 16:20
As a hint for that, you could create a raise_notice() function and call that from inside a case statement. – Chris Travers Jun 16 '16 at 04:12
Thanks but it's beyond my capabilities right now and this is just a drop in the ocean compared to everything else I need to do... some day when I have something more significant to do in psql I'll take the time to learn how to use it well. – Ed Morton Jun 16 '16 at 05:11

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

1 Answers1