CODEWITHSUNDEEP
postgresqlstored-procedurespostgresql-9.1plpgsql
Naveen
Naveen

Reputation: 159

Get result from query in DO satement

How to run SQL statement within an IF condition in plpgsql? I don't want to create or replace a function. This is what I tried:

DO LANGUAGE plpgsql $$
BEGIN
IF 'Khosla' = 'Khosla' THEN
SELECT * FROM test_log limit 10;
ELSE
RAISE NOTICE 'not worked';
END IF;
END;
$$;

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 3 at SQL statement

I also tried this but was unable to get data:

DO LANGUAGE plpgsql $$
BEGIN
if 'a' = 'a' THEN
PERFORM * FROM test_log limit 10;
else
raise notice 'not worked';
end if;
end;
$$;

Output: Query returned successfully with no result in 13 ms.

After reading document properly I came to know that PERFORM executes SQL and discards result set.

Can you help in running the SQL Statement and getting the result set?

Upvotes: 1

Views: 3949

Answers (2)

Naveen
Naveen

Reputation: 159

Finally I Did IT - Passing Parameter in plpgsql function.

CREATE OR REPLACE FUNCTION param_test_1(text)
RETURNS TABLE (comp_id int, comp_name text, comp_sort text) AS $$
BEGIN
IF 'company' = $1 THEN
RETURN QUERY SELECT id::int as comp_id, ltrim(company.compname)::text as compname, ltrim(company.compname)::text as sort FROM company where deleteflag = '0' and active = '1' and id in (270,394,376,396,403);
ELSEIF 'jobs' = $1 THEN
RAISE NOTICE 'Not Working';
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM param_test_1('company');

This is working perfectly. Any suggestions for better approach on my work will be highly appreciated. Thanks

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

You cannot return values from a DO statement. Create a plpgsql function instead.

There are several ways to define the return type with the RETURNING clause or with OUT parameters. Read the manual about CREATE FUNCTION.

There are several ways to return values from the function. Read the chapter Returning From a Function in the manual.

In particular, since you are trying to return whole rows from a table, you can use the registered type of the table for the function declaration:

CREATE FUNCTION foo ()
  RETURNING SETOF test_log 
$func$
BEGIN

IF 'a' = 'a' THEN
  RETURN QUERY
  SELECT * FROM test_log LIMIT 10;
ELSE
   RAISE WARNING $$Didn't work!$$;
END IF;

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM foo ();

Or try a search here on SO. I posted many related code examples.

Workaround for DO statement

If you cannot use a function, the only halfway sensible workaround with a DO statement is to use a temporary table:

CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM test_log LIMIT 0;

$do$
BEGIN

IF 'a' = 'a' THEN
  INSERT INTO tbl_tmp
  SELECT * FROM test_log LIMIT 10;
ELSE
   RAISE WARNING $$Didn't work!$$;
END IF;

END
$do$ LANGUAGE plpgsql;

SELECT * FROM tbl_tmp;

Temporary tables are dropped automatically at the end of the session.

Upvotes: 1

Related Questions