Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

PostgreSQL: declare variables

Hello experts,
I was wondering how you declare variables in PostgreSQL:
My attemp is the following
Declare @tablename;
Set @tablename = "tablename";
Select * from db.@tablename

Open in new window

And I have the following error message.
ERROR:  syntax error at or near "@"
LINE 1: Declare @tablename;
                ^

********** Error **********

ERROR: syntax error at or near "@"
SQL state: 42601
Character: 9
Thank you in advance for your help.
Avatar of Bill Prew
Bill Prew

I think you have two issues.  Based on the error I suspect the @ symbol is not allowed in variable names, or at least not as the first character.

In additional I think you need to specify the type of the variable you are declaring, like:

Declare tablename varchar;


»bp
Avatar of Luis Diaz

ASKER

Thank you Bill,
I tried the following:
DO $$
DECLARE v_tablename varchar;
BEGIN
  v_tablename := "test" ;
  SELECT *
  FROM   dbo.v_tablename;
  -- ...
END $$;

Open in new window

And I have the following error message:

ERROR:  column "test" does not exist
LINE 1: SELECT "test"
               ^
QUERY:  SELECT "test"
CONTEXT:  PL/pgSQL function inline_code_block line 4 at assignment

********** Error **********

ERROR: column "test" does not exist
SQL state: 42703
Context: PL/pgSQL function inline_code_block line 4 at assignment

Thank you in advance for your help.
Well, I'm not a comprehensive expert in PostgreSQL so can't go too far on the follow up questions.  I do know that it's quite a bit different than some of the other SQL varieties like SQL Server, etc.  And I doubt that it will let you pull the name of the table in a FROM statement from a variable the way you have tried.  Typically that requires some some of a dynamic SQL approach with a "prepare" or "execute" method.

If you are going to be working with PostgreSQL some, I'd recommend reading up on the basics with a free online tutorial, or taking a low cost self study web based course.  I think you will find it gets you to a point where you can start to see some results quicker and over the basic stuff.  Just my approach / opinion thought...


»bp
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Noted, thank you very much for your help.