Variables inside PSQL script without creating a Function

Question

I'm trying to get a PSQL script running using variables in an example like the one below without declaring functions and having to call them.

DECLARE
    result TEXT;
BEGIN
    SELECT INTO result name 
FROM test;

    RAISE NOTICE result;
END;

Where table test only has 1 row and column. Is this possible without having to wrap this script inside a function. This will allow me to call the script via say command line easier.

Thanks guys.

Answer 1

You can use DO to create and execute an anonymous function:

DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.

Something like this:

do $$
    declare result text;
    begin
        select name into result from test;
        raise notice '%', result;
    end;
$$;

I also fixed your raise notice.

If you just want to dump the single value from the table to the standard output in a minimal format (i.e. easy to parse), then perhaps --tuples-only will help:

-t
--tuples-only
Turn off printing of column names and result row count footers, etc. This is equivalent to the \t command.

So you could say things like this from the shell:

result=$(echo 'select name from test;' | psql -t ...)