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.
You can use DO to create and execute an anonymous function:
DOexecutes 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\tcommand.
So you could say things like this from the shell:
result=$(echo 'select name from test;' | psql -t ...)
ERROR: syntax error at or near "do" LINE 1: do $$... we're using PostreSQL v8.3. Would there be an equivalent for that version?
psql -t as in my update.