0

I am new to PostgreSQL. I have a doubt about PostgreSQL script .

In my last question i was trying to use "execute" . Then i come to know , If i want to use Execute in query i have to make script (use $$ LANGUAGE ... ).

But one answer from similar questions , use query_to_xml and it not need script . why ?

Jophy job
  • 1,924
  • 2
  • 20
  • 38
  • It's an odd workaround from having to use dynamic SQL to do what you are doing. I think the last answer [here](https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres) sums up how it works. Like anything in SQL there is no "ONE" way to do something. Often times there are many ways to get the results you want each with their own caveats. – JNevill Mar 14 '18 at 15:18
  • @JNevill but in MSSQL server we not have to do this things (if want to run one time run sql command , want t execute again and again put in stored procedure or function ) .. why PostgreSQL need this ? – Jophy job Mar 14 '18 at 15:23

1 Answers1

3

Unlike SQL Server, Postgres (and many other DBMS like Oracle, DB2, Firebird) makes a clear distinct between procedural code and SQL. Procedural code can only be run in the context of a function (or procedure). A do block is essentially an anonymous function that doesn't return anything.

Dynamic SQL can only be used in procedural code. query_to_xml does exactly that: it uses dynamic SQL.

To count the rows in a table you could also create a function that uses dynamic SQL:

create function count_rows(p_schemaname text, p_tablename text)
  returns bigint
as
$$ 
declare
  l_stmt text;
  l_count bigint;
begin
  l_stmt := format('select count(*) from %I.%I', p_schemaname, p_tablename);
  execute l_stmt
    into l_count;
  return l_count;
end;
$$
language plpgsql;

You can then use:

select schema_name, table_name, count_rows(schema_name, table_name)
from information_schema.tables
where schema_name = 'public';

query_to_xml does essentially the same thing as the function count_rows() - it's just a generic function to run any SQL statement and return that result as XML, rather than a specialized function that only does exactly one thing.

  • Is there a reason why there is no `query_to_jsonb`? – Bergi Apr 09 '20 at 18:47
  • @Bergi: because nobody implemented it, but it's really easy to do http://blog.sql-workbench.eu/post/query-to-json/ –  Apr 09 '20 at 19:51