Re: [GENERAL] how to pass tablename to a function

2014-09-05 Thread Igor Neyman 


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of alecinvan
Sent: Thursday, September 04, 2014 6:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] how to pass tablename to a function

Hi, All

I like to pass the tablename to function but not using execute clause, here is 
my script

CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text, IN 
jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN stopid 
bigint)
  RETURNS TABLE(events bigint, errors bigint[]) AS $BODY$ BEGIN

RETURN QUERY
WITH
 unpacking (raw_id, time, userid, eventtype, pagename, userhost, 
application, status, error)
 AS (
select 
  id as raw_id, 
  (up).time, 
  (up).userid,
  coalesce((up).eventtype, ''),
  coalesce((up).pagename, ''),
  (up).userhostaddress as userhost,
  coalesce((up).application, ''),
  (up).status, 
  (up).error
 from(
  select id, 
 functions.python_events_unpack(event_object) up
  from  _tbl 
  where id between startid and stopid
 ) a 
 where (up).userid is not NULL 
  )


I want to pass the _tbl to the select query integrated in the unpacking(), how 
can I make it?

thanks

Alec


You can't.
You have to use dynamic sql (EXECUTE).

Regards,
Igor Neyman


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to pass tablename to a function

2014-09-04 Thread A L . 
Hi, All


I like to pass the tablename to function but not using execute clause, here is 
my script


CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN
 _tbl text, IN jobid bigint, IN jobtime timestamp with time zone, IN 
startid bigint, IN stopid bigint)

  RETURNS TABLE(events bigint, errors bigint[]) AS

$BODY$

BEGIN


RETURN QUERY

WITH

 unpacking (raw_id, time, userid, eventtype, pagename, userhost, 
application, status, error)

 AS (

select 

  id as raw_id, 

  (up).time, 

  (up).userid,

  coalesce((up).eventtype, ''),

  coalesce((up).pagename, ''),

  (up).userhostaddress as userhost,

  coalesce((up).application, ''),

  (up).status, 

  (up).error

 from(

  select id, 

 functions.python_events_unpack(event_object) up

  from  _tbl 

  where id between startid and stopid

 ) a 

 where (up).userid is not NULL 

  )



I want to pass the _tbl to the select query integrated in the unpacking(), how 
can I make it?


thanks


Alec

Re: [GENERAL] how to pass tablename to a function

2014-09-04 Thread Adrian Klaver 

On 09/04/2014 04:42 PM, A L. wrote:

Hi, All

I like to pass the tablename to function but not using execute clause,
here is my script

CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl
text, IN jobid bigint, IN jobtime timestamp with time zone, IN startid
bigint, IN stopid bigint)
   RETURNS TABLE(events bigint, errors bigint[]) AS
$BODY$
BEGIN

RETURN QUERY
WITH
  unpacking (raw_id, time, userid, eventtype, pagename, userhost,
application, status, error)
  AS (
 select
   id as raw_id,
   (up).time,
   (up).userid,
   coalesce((up).eventtype, ''),
   coalesce((up).pagename, ''),
   (up).userhostaddress as userhost,
   coalesce((up).application, ''),
   (up).status,
   (up).error
  from(
   select id,
  functions.python_events_unpack(event_object) up
   from  _tbl
   where id between startid and stopid
  ) a
  where (up).userid is not NULL
   )


I want to pass the _tbl to the select query integrated in the
unpacking(), how can I make it?


Assuming you are using plpgsql, see here:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

or use a language like plpythonu.



thanks

Alec



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to pass tablename to a function

2014-09-04 Thread David G Johnston 
alecinvan wrote
 I like to pass the tablename to function but not using execute clause,
 here is my script
 
 [...]
 
 I want to pass the _tbl to the select query integrated in the unpacking(),
 how can I make it?

There is no way to perform a query with an unknown, at design time,
identifier without using EXECUTE.

The recommended way to do this is to use format() and dollar-quoting -
v9.1+ required:

v_qry := format(

$qry$
WITH [...]
SELECT id, func(...) FROM %I WHERE id [...]
$qry$

, _tbl

);

RETURN QUERY EXECUTE v_qry;

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-tablename-to-a-function-tp5817864p5817871.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to pass tablename to a function

2014-09-04 Thread alecinvan 
Hi, All

I like to pass the tablename to function but not using execute clause, here
is my script

CREATE OR REPLACE FUNCTION functions.pgsql_event_unpack_batch(IN _tbl text,
IN jobid bigint, IN jobtime timestamp with time zone, IN startid bigint, IN
stopid bigint)
  RETURNS TABLE(events bigint, errors bigint[]) AS
$BODY$
BEGIN

RETURN QUERY
WITH
 unpacking (raw_id, time, userid, eventtype, pagename, userhost,
application, status, error)
 AS (
select 
  id as raw_id, 
  (up).time, 
  (up).userid,
  coalesce((up).eventtype, ''),
  coalesce((up).pagename, ''),
  (up).userhostaddress as userhost,
  coalesce((up).application, ''),
  (up).status, 
  (up).error
 from(
  select id, 
 functions.python_events_unpack(event_object) up
  from  _tbl 
  where id between startid and stopid
 ) a 
 where (up).userid is not NULL 
  )


I want to pass the _tbl to the select query integrated in the unpacking(),
how can I make it?

thanks

Alec






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-pass-tablename-to-a-function-tp5817861.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general