CODEWITHSUNDEEP
postgresqlpostgis
Cyrus the Great
Cyrus the Great

Reputation: 5932

Select query by declare variable on postgresql

I am new working postgresql and pgadmin4.I write a very simple query.

I have a table called PgFinalLocationsTable on public schema.In my table there are a few filed.One of these filed is UserName .I want to declare a variable and finally do select on my table according this variable like below:

DO $$
DECLARE myvar text default 'sa';
BEGIN
    select * from public."PgFinalLocationsTable" where "UserName" = myvar;
END $$;

But why i got these message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

It is a simple query!!!

After googling and see post on stack i have changed my query like this:

CREATE OR REPLACE FUNCTION fun(text myvar) RETURNS text AS $$
--DECLARE myvar text;
BEGIN
    select * from public."PgFinalLocationsTable" where "UserName" = myvar;
END;
$$ language plpgsql;

select fun('sa');

I want to return all my fields and i do not want to use plpgsql.I want to use PostgreSQL. In any case i got this error:

ERROR:  type myvar does not exist
SQL state: 42704

What is the problem on my first query and second query?Should i have make a function for select query when i want to pass a variable?

I do all stuff because i want to create this sql query:

"IF (NOT EXISTS(SELECT 1 FROM [dbo].[{0}] WHERE [UserId] = @UserId And [DeviceId] = @DeviceId)) " +
"BEGIN " +
"INSERT INTO [dbo].[{0}]([Id], [Location], [Timestamp], [UserId], [DeviceId], [AllowDomains], [Topic], [UserName], [FirstName], [LastName], [JobLocationName], [LocationId], [AppVersion], [AppName]) " +
"VALUES(@Id, GEOGRAPHY::Point(@X, @Y, 4326), @Timestamp, @UserId, @DeviceId, @AllowDomains, @Topic, @UserName, @FirstName, @LastName, @JobLocationName,  @LocationId,  @AppVersion, @AppName) " +
"END "

Upvotes: 3

Views: 7943

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45760

You don't understand to DO command well. DO command is anonymous function without declaration, and because it has not declared an output, then is not possible any other result than debug stream.

so your first example has not sense in PostgreSQL. Result of unbind queries in MSSQL is returned as result of MS SQL procedure. Nothing similar is possible in PostgreSQL. PostgreSQL knows only functions, that can returns scalar value, composite value or relation (only one). When you are coming from MS SQL, the best what you can, try to forgot almost all knowleadge from MS SQL. 

ERROR:  type myvar does not exist
SQL state: 42704

This bug is clean - you switch variable name and type name - really type myvar doesn't exist.

Some function that returns table can looks like:

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
BEGIN
  RETURN QUERY SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = myvar;
END;
$$ LANGUAGE plpgsql;

or you can use a SQL language only

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
  SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = $1;
$$ LANGUAGE sql;

Because PostgreSQL doesn't support unbind queries, then doesn't allow it. You should to use RETURN QUERY command - in PLpgSQL language.

Because programming with stored procedures is really different between PostgreSQL and MSSQL (MSSQL is not similar to any other), please, try to read documentation - it is not bad https://www.postgresql.org/docs/current/static/plpgsql.html

Your function can looks in Postgres like (I don't know used types)

CREATE OR REPLACE FUNCTION fx("_UserId" int,
                              "_DeviceId" int,
                              "_X" int,
                              "_Y" int, 
                              ...
BEGIN
  IF NOT EXISTS(SELECT * FROM /* I don't know what [{0}] means */
                 WHERE "UserId" = "_UserId" AND "DeviceId" = "_DeviceId")
  THEN
    INSERT INTO ..
  END IF;
END;
$$ LANGUAGE plpgsql;

Probably your fragment can be solved without procedural extension by INSERT INTO ON CONFLICT DO NOTHING command https://www.postgresql.org/docs/current/static/sql-insert.html - what is better.

Note - using case sensitive identifiers is short way to hell.

Upvotes: 4

Related Questions