Postgresql – Pass variable as an input argument to a function inside another function in PostgreSQL

functionsparameterpostgresql

I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible…)

Inside a function I am trying to assign a value from calling another function like this:
left_bipolar_n:=public.convert_to_decimal(left_bipolar);

When I call the function, If I use something like '122' as an argument instead of "left_bipolar" it works just fine but just as it is I get:
Error: invalid input syntax for type numeric: " "

I should say that Function convert_to_decimal expects text

Complete code:

-- FUNCTION: public.AmateurExercise(numeric)
--drop FUNCTION public."AmateurExercise"(numeric);

CREATE OR REPLACE FUNCTION public."AmateurExercise"(
    n numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare 
bipolar text;
tamanho integer;
left_bipolar text;
right_bipolar text;
left_bipolar_n numeric;
right_bipolar_n numeric;

begin
bipolar:=public.convert_to_bipolar8(n);
tamanho=length(bipolar);
if tamanho/2 =floor (tamanho/2) then 
begin
left_bipolar=left(bipolar,tamanho/2);
right_bipolar=right(bipolar, tamanho/2);
end;
else
left_bipolar=left(bipolar,(tamanho-1)/2);
right_bipolar=right(bipolar, ((tamanho-1)/2)+1);
end if;

left_bipolar_n:=public.convert_to_decimal(left_bipolar);
return left_bipolar_n;

end;
$BODY$;

ALTER FUNCTION public."AmateurExercise"(numeric)
    OWNER TO postgres;

Best Answer

answer from a jjanes comment:

"The error indicates that for some reason left_bipolar contains a space just before the call. I guess convert_to_bipolar8 must put the space there. But we can't see the code for it, not what input you called the outer function with"

Related Solutions

Postgresql – Calling another function inside a postgres function

Your syntax is twisted in several ways. If the return type of function A is defined as RETURNS TABLE (id int, address text), it could work like this:

Select A.address, B.name 
FROM   f_a() A
JOIN   B USING (id);

Aside: While your identifiers look alright for the purpose, I strictly use legal, lower case identifiers to avoid complications.

Postgresql – Make query depend on input in PostgreSQL function

If I understand correctly, you can sometimes provide _company_id directly, while you only have _company_name in other situations. You do not intend to provide both at once.

Since one parameter is of type text while the other is integer the most efficient solution for this case is probably function overloading. I.e., create two functions of the same name, one for text and another one for integer.

While being at it, I suggest simple SQL functions for the simple task. The principal is the same as for plpgsql.

json_agg() returns data type json, not text. Your plpgsql variant silently coerces the type in the assignment, but that's bad style. I made out the functions to return json directly. You can cast to text explicitly if you need to.

1) `companies_all_teams(text)`

CREATE OR REPLACE FUNCTION companies_all_teams(_company_name text)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   companies c
   JOIN   jobs      j ON j.company_id = c.id
   JOIN   teams     t ON t.id = j.team_id
   WHERE  lower(c.name) = lower(_company_name)
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

2) `companies_all_teams(int)`

Since we have two functions, we can optimize each for their input. Assuming referential integrity in your relational design, we don't need to join to companies at all, we already know the company_id:

CREATE OR REPLACE FUNCTION companies_all_teams(_company_id int)
  RETURNS json AS
$func$
   SELECT json_agg(DISTINCT(t.name))
   FROM   jobs      j
   JOIN   teams     t ON t.id = j.team_id
   WHERE  j.company_id = _company_id
   AND    NOT j.is_disabled
$func$ LANGUAGE sql;

To avoid ambiguity with overloaded function calls you need to provide explicitly typed values. In your simple case, however, it's still enough to use numeric literals (no single quotes) for integer or string literals (single quotes) for text.

Call:

SELECT companies_all_teams(123);           -- numeric literal without quotes
SELECT companies_all_teams('My Company');  -- string literal with quotes

Best Answer

Related Solutions

Postgresql – Calling another function inside a postgres function

Postgresql – Make query depend on input in PostgreSQL function

1) companies_all_teams(text)

2) companies_all_teams(int)

Related Question

1) `companies_all_teams(text)`

2) `companies_all_teams(int)`