Return results from anonymous code block on Postgresql

One of the nice things about MS Sql Server procedural language is that it doesn't support multiple languages, T-SQL only, and as such it can facilitate on-the-fly testing of code logic:

declare @person_id int;

set @person_id = 1;

select * from person where person_id = @person_id;

On Postgres 9.0, though it already supports anonymous code block, it's constrained by the fact that the anonymous code block is encapsulated in an invisible void function(Postgres supports many procedural languages, not just its own flavor of procedural language(PL/pgSQL), e.g. Python, Java, Perl, LOLCODE, etc), well you can think of it that way. Hence displaying query results on anonymous code block won't work out-of-the-box on Postgres. This won't work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 select * from person
 where person_id = _person_id;
end;
$$;

That will produce this error:

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

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

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

Neither this will work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 return query select * from person
 where person_id = _person_id;
end;
$$;

That code will produce this error:

ERROR:  cannot use RETURN QUERY in a non-SETOF function
LINE 13:  return query select * from person where person_id = _person...
          ^

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

ERROR: cannot use RETURN QUERY in a non-SETOF function
SQL state: 42804
Character: 170

So if you want to test logic on-the-fly on Postgres, create temporary table on anonymous code block:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 drop table if exists _x;
 create temporary table _x as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

---

Wish for Postgres, make this happen, to make adhoc-y stuff happen :-)

do returns table (person_id int, lastname text) as
$$
declare
 _person_id int; 
begin
 _person_id = 1;
 
 return query select person_id, lastname from person
 where person_id = _person_id;
end;
$$;

---

UPDATE: March 27, 2019

Made the code shorter:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 create temporary table _x on commit drop
 as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

With that code, there's no need to explicitly drop the table. Postgres has an implicit commit whenever queries are executed. on commit drop ensures that the temporary table will not stay in session longer than necessary.


Found out on commit drop here: https://stackoverflow.com/questions/22929365/create-a-temp-table-if-not-exists-for-use-into-a-custom-procedure/22932917#22932917

Using NHibernate 3 on Fluent NHibernate 2.0.0.967

If you are like most folks who want to experiment on latest and bleeding edge technology, encountering errors and making your way around it is the way of life. At the time of this writing, you will encounter this error...

cannot implicitly convert type 'NHibernate.ISessionFactory' to 'NHibernate.ISessionFactory'. An explicit conversion exists (are you missing a casts?)

...when you attempt to use Fluent NHibernate 2.0.0.967 on NHibernate 3. The code in question:

static ISessionFactory CreateSessionFactory()
{
 return Fluently.Configure()
  .Database
   (
    PostgreSQLConfiguration.Standard.ConnectionString("Server=localhost;Database=fluent_try;User ID=postgres;Password=xxxx;")
   )
  .Mappings( m => m.FluentMappings.AddFromAssemblyOf<MainClass>() )    
  .BuildSessionFactory();
}

Which seems odd, considering that BuildSessionFactory's method signature is:

public ISessionFactory BuildSessionFactory();
    

Though Fluent NHibernate 2.0.0.967 was built for NHibernate 2.1, the following would do the trick for convincing the compiler that NHibernate 3 is compatible with NHibernate 2.1:

static ISessionFactory CreateSessionFactory()
{
 return (ISessionFactory) Fluently.Configure() // note the explicit cast
  .Database
   (
    PostgreSQLConfiguration.Standard.ConnectionString("Server=localhost;Database=fluent_try;User ID=postgres;Password=xxxxxxxxx;")
   )
  .Mappings( m => m.FluentMappings.AddFromAssemblyOf<MainClass>() )    
  .BuildSessionFactory();
}

Happy Fluenting! :-)

EDIT: 2010-12-10 Apparently, the explicit cast trick works on Mono only, on Visual Studio, it will prompt you to add binding redirect records to app.config

UPDATE: 2010-12-13 There's now an NHibernate 3-compatible Fluent NHibernate: http://fluentnhibernate.org/dls/v1.x/fluentnhibernate-NH3.0-binary-1.2.0.694.zip

Here's the sample code: http://www.ienablemuch.com/2010/12/nhibernate-3-fluent-linq-one-stop-shop.html

Fluent NHibernate: cannot convert lambda expression error

If you received this kind of error...

Cannot convert 'lambda expression' to non-delegate type Options

or this:

Error CS1660: Cannot convert `lambda expression' to non-delegate type `System.Linq.Expressions.Expression<System.Func<Product,object>>' (CS1660)

...just add System.Core in your References

Performing ORDER BY on DISTINCT on Linq to NHibernate(version 3)

You will receive errors when doing OrderBy on Distinct result on Linq to NHibernate (NHibernate 3) at the time of this writing:

var cat = session.Query<Product>().Select(x => x.Category).Distinct().OrderBy(s => s);

Convert it to:

var cat = session.Query<Product>().Select(x => x.Category).OrderBy(s => s).Distinct();

Alternatively you can do this, which is quite neat:

var cat = 
        (from c in session.Query<Product>()
        orderby c.Category 
        select c.Category).Distinct();

Note the last two codes produces this(which is performant):

select distinct category 
 from product 
 order by category asc

Not this:

select distinct category
 from 
 (select category from product
 order by category)

Be aware that if you are using Linq to SQL, the 3rd code construct cannot build a proper query (ORDER BY is omitted on generated query, silent error). Documented here: http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

He advises to remove the orderby out of Linq and move it after of .Distinct() extension method.

var cat = (from c in db.Products   
    select c.Category).Distinct().OrderBy(s => s);

Which leads to attaching two extension methods on the query just to make Linq to SQL emit the correct SQL. Which on my book, renders the whole exercise of making Linq as query-like as possible a pointless one.

And that valid Linq to SQL code is invalid in NHibernate's Linq(produces error, see below). And funny as it is, the valid NHibernate Linq is invalid in Linq to SQL, and vice versa; though Linq to SQL doesn't produce any exceptions, it's just as quite annoyance as it is, it's a silent error, the code goes to production and it has error yet you don't know it, Linq to SQL doesn't fail fast.


I prefer the Linq to NHibernate approach than Linq to SQL. Not because NHibernate is database-agnostic(but it certainly adds appeal), but for the reason that it dutifully informs the programmer that if it cannot do something it won't do silent errors. It fail fast.

Here's the error emitted when performing OrderBy on Distinct expression:

Unhandled Exception: System.NotSupportedException: Operation is not supported.
  at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName (IASTNode querySource) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process (IASTNode tree) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process () [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process (IASTNode ast, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators (IASTNode ast, System.String queryIdentifier, System.String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators (System.String queryIdentifier, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan.CreateTranslators (System.String expressionStr, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor (System.String expressionStr, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor (System.String expressionStr, IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan (IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) [0x00000] in <filename unknown>:0 
  at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan (IQueryExpression queryExpression, Boolean shallow) [0x00000] in <filename unknown>:0 
  at NHibernate.Impl.AbstractSessionImpl.CreateQuery (IQueryExpression queryExpression) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.PrepareQuery (System.Linq.Expressions.Expression expression, IQuery& query, NHibernate.Linq.NhLinqExpression& nhQuery) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.Execute[IEnumerable`1] (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Remotion.Data.Linq.QueryableBase`1[System.String].GetEnumerator () [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[System.String].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/corlib/System.Collections.Generic/List.cs:126 
  at System.Collections.Generic.List`1[System.String]..ctor (IEnumerable`1 collection) [0x0002f] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/corlib/System.Collections.Generic/List.cs:63 
  at System.Linq.Enumerable.ToList[String] (IEnumerable`1 source) [0x00006] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/System.Core/System.Linq/Enumerable.cs:2851 
  at TestTwoTable.Program.PhoneListUsingLinq () [0x000ae] in /Volumes/SHARED/SharedTests/TestTwoTable/TestTwoTable/Program.cs:138 
  at TestTwoTable.Program.Main (System.String[] args) [0x00000] in /Volumes/SHARED/SharedTests/TestTwoTable/TestTwoTable/Program.cs:20 

Keyword search: nhibernate linq select distinct

Unix transport error when unit testing NHibernate for Postgresql under Mono

If you happen to encounter the following error while doing unit testing for NHibernate for Postgresql under Mono...

Internal error
        RemotingException: Unix transport error.

...Change your Npgsql version to a Mono one, then that error won't happen.

That error will appear on unit testing if you are using MS .NET version of Npgsql (example: http://pgfoundry.org/frs/download.php/2868/Npgsql2.0.11-bin-ms.net4.0.zip) under Mono. When unit testing under Mono, you must use Mono version of Npgsql (example: http://pgfoundry.org/frs/download.php/2860/Npgsql2.0.11-bin-mono2.0.zip)

Weird problem, the error only appear when the code is run under Unit Testing(built-in in MonoDevelop). But when run independently, MS .NET version of Npgsql will run just fine under Mono. Anyway, to make matters simple, use Mono version of the component if you are building Mono stuff

---