Postgres: use function that returns a string in another function

Question

I've a PostgreSQL function that returns a string. I want to use this function into another one but I obtain an error.

These are the sample functions, with get_some_string that returns text, and use_the_string where I want to call the previous one and store the result in a variable:

CREATE OR REPLACE FUNCTION public.get_some_string()

RETURNS text AS
$func$
DECLARE

BEGIN

  return 'mystring';

END
$func$ LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE FUNCTION public.use_the_string()

RETURNS boolean AS
$func$
DECLARE

  mytext text;

BEGIN

  mytext := select public.get_some_string();

END
$func$ LANGUAGE plpgsql VOLATILE;

If I run this query I obtain the error:

ERROR:  syntax error at or near "select"
LINE 24:   mytext := select public.get_some_string();

What I'm doing wrong? How can I use the return value of the first function into the second one?

mytext := (select public.get_some_string());

Vao Tsun
– Vao Tsun

2017-11-17 11:56:07 +00:00
Commented Nov 17, 2017 at 11:56 — Vao Tsun
– Vao Tsun, Commented Nov 17, 2017 at 11:56

user330315 · Accepted Answer · 2017-11-17 11:59:13Z

3

You don't need a select:

CREATE OR REPLACE FUNCTION public.use_the_string()

RETURNS boolean AS
$func$
DECLARE

  mytext text;

BEGIN

  mytext := public.get_some_string();

END
$func$ LANGUAGE plpgsql VOLATILE;

answered Nov 17, 2017 at 11:59

user330315

Sign up to request clarification or add additional context in comments.

Comments

JustMe · Accepted Answer · 2017-11-17 12:16:42Z

2

The second one must be:

RETURNS boolean AS
$func$
DECLARE

  mytext text;

BEGIN

  select public.get_some_string() into mytext;

END
$func$ LANGUAGE plpgsql VOLATILE;

or

mytext := (select public.get_some_string());

or

mytext := public.get_some_string();

edited Nov 17, 2017 at 12:16

answered Nov 17, 2017 at 11:54

JustMe

2,3833 gold badges25 silver badges46 bronze badges

Comments

Abelisto · Accepted Answer · 2017-11-18 21:50:13Z

1

Note to other answers: select is implicitly added in plpgsql context. Example:

do $$
declare
  v text[];
begin
  v := array_agg(datname) from pg_database;
  raise info 'List of databases: %', v;
end $$;

So when you call mytext := select public.get_some_string(); it is transformed to select select public.get_some_string(); internally.

Thats why parenthesesis mytext := (select public.get_some_string()); could be the solution: select (select public.get_some_string()); is the acceptable statement.

edited Nov 18, 2017 at 21:50

answered Nov 17, 2017 at 13:00

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

Postgres: use function that returns a string in another function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related