CODEWITHSUNDEEP
postgresqlplpgsql
lentyai
lentyai

Reputation: 690

PostgresQL Error: relation <table> doesn't exist

I'm new to Postgres and plpsql, so sorry if the answer is obvious. However I wasn't able to figure out what I'm doing wrong here. When I use the sample DB function, everything is fine. When I try to create my own: 

CREATE OR REPLACE FUNCTION get_trans (p_param VARCHAR) 
  RETURNS TABLE (
    amount money,
    transactionId UUID
  ) 
  AS $$
  BEGIN
    RETURN QUERY SELECT
      amount,
      transactionId
    FROM
    transactions;
  END; $$ 

LANGUAGE 'plpgsql';

The function executes, but testing it by running the following test statement fails: 

SELECT * 
FROM public.get_trans(
  'bs'
)

with this error:

ERROR:  relation "public.transactions" does not exist
LINE 4:  FROM public.transactions
          ^
QUERY:  SELECT
          amount,
          transactionId
FROM public.transactions
CONTEXT:  PL/pgSQL function get_trans(character varying) line 4 at RETURN 
QUERY
SQL state: 42P01

It looks like it's a pretty common error with plpsql, and I realize that I'm probably missing smth obvious, but none of the answers so far helped in my case. is a straight forward stand-along table with only primary key for now.

Any tips are highly appreciated.

UPDATE: So, thanks to LD I found out that my table although created doesn't exist in any schema due to syntax errors. It turned out postgres is more different from MSSQL as I thought, and harder to flash out script errors. The main error was - you can't use camel-case in table or column definition. Once I fixed those and rerun the function, everything as fine.

Upvotes: 1

Views: 6921

Answers (1)

Łukasz Duda
Łukasz Duda

Reputation: 179

Function itself seems be ok. Please ma sure the table transactions exists in public schema by using(check the column table_schema in results):

SELECT * 
FROM information_schema.tables 
WHERE  table_name = 'transactions'

Upvotes: 1

Related Questions