Re: [GENERAL] Function fixing - PostgreSQL 9.2

[drum.lu...@gmail.com]

>
>
>>> *Question:*

 Payments in a Pending state cannot be invoiced and are excluded from
 the Invoice Runs section, but they are showing in the count mechanic.

 How can I solve this?

>>>
>>> ​In 9.2 you probably need to convert the count into a conditional sum:
>>>
>>> SELECT sum(CASE WHEN  THEN 1 ELSE 0 END) FROM data;
>>>
>>> You can probably do the same with count since it excludes nulls.
>>>
>>> SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;
>>>
>>> ​9.4 introduced a FILTER clause for Aggregate Expressions that can do
>>> this much more cleanly and efficiently.​
>>>
>>> ​http://www.postgresql.org/docs/9.4/static/sql-expressions.html​
>>>
>>> David J.
>>>
>>>
>>
> ​I barely scanned your original query - just read the description.  I
> don't have the inclination - especially without a self-contained example -
> to make changes to it.
>
> David J.
> ​
>
>
>
Just an update - Hope this help you to get more data:


There is a number beside each color flag, and a number in the "sub status"
drop down. Instead of "the number of jobs that have billable items that can
be invoiced, it shows the "the number of jobs that have "something"
billable but not yet invoiced."

And since those payments cannot be invoiced alone, they won't show up in
the list.

This is the Query:

> public function search($type=self::SEARCH_TYPE_STATUS_TYPE, &$rawData =
> []){
> if($type === self::SEARCH_TYPE_STATUS_TYPE) {
> $fields = "
> stj.status_type_id,
> stj.jobs_count,
> stj.job_ids,
> (
> SELECT
> array_to_json(array_agg(srcs))
> FROM
> (
> -- property names in the json match column names in g_statuses_jobs()
> SELECT
> (srs.sr[1]::BIGINT) AS status_id,
> (srs.sr[2]::TEXT) AS status_label,
> (srs.sr[3]::BOOLEAN) AS status_is_default,
> (srs.sr[4]::BIGINT) AS jobs_count,
> (srs.sr[5]::JSON) AS job_ids
> FROM
> (SELECT regexp_split_to_array(regexp_split_to_table(stj.status_type_data,
> E'\n'), E'\t')) AS srs(sr)
> ) AS srcs
> ) AS status_type_json
> ";
> $searchFunction =
>  
> 'g_status_types_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)
> AS stj';
> $factory = new JobSearchStatusSummaryFactory();
> }else{
> $fields = '*';
> $searchFunction =
>  
> "g_statuses_jobs($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)";
> $factory = new JobSearchResultFactory();
> }
> $query = "SELECT
> $fields
> FROM $searchFunction";

Re: [GENERAL] Function fixing - PostgreSQL 9.2

[David G. Johnston]

On Mon, Feb 29, 2016 at 6:14 PM, drum.lu...@gmail.com 
wrote:

>
>
> On 1 March 2016 at 11:35, David G. Johnston 
> wrote:
>
>> On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com <
>> drum.lu...@gmail.com> wrote:
>>
>>> *Question:*
>>>
>>> Payments in a Pending state cannot be invoiced and are excluded from the
>>> Invoice Runs section, but they are showing in the count mechanic.
>>>
>>> How can I solve this?
>>>
>>
>> ​In 9.2 you probably need to convert the count into a conditional sum:
>>
>> SELECT sum(CASE WHEN  THEN 1 ELSE 0 END) FROM data;
>>
>> You can probably do the same with count since it excludes nulls.
>>
>> SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;
>>
>> ​9.4 introduced a FILTER clause for Aggregate Expressions that can do
>> this much more cleanly and efficiently.​
>>
>> ​http://www.postgresql.org/docs/9.4/static/sql-expressions.html​
>>
>> David J.
>>
>>
> Thank you David...
>
> Can you please show me how it would be with the new changes?
>
>
​I barely scanned your original query - just read the description.  I don't
have the inclination - especially without a self-contained example - to
make changes to it.

David J.
​

Re: [GENERAL] Function fixing - PostgreSQL 9.2

[drum.lu...@gmail.com]

On 1 March 2016 at 11:35, David G. Johnston 
wrote:

> On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>> *Question:*
>>
>> Payments in a Pending state cannot be invoiced and are excluded from the
>> Invoice Runs section, but they are showing in the count mechanic.
>>
>> How can I solve this?
>>
>
> ​In 9.2 you probably need to convert the count into a conditional sum:
>
> SELECT sum(CASE WHEN  THEN 1 ELSE 0 END) FROM data;
>
> You can probably do the same with count since it excludes nulls.
>
> SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;
>
> ​9.4 introduced a FILTER clause for Aggregate Expressions that can do this
> much more cleanly and efficiently.​
>
> ​http://www.postgresql.org/docs/9.4/static/sql-expressions.html​
>
> David J.
>
>
Thank you David...

Can you please show me how it would be with the new changes?

Thanks

Re: [GENERAL] Function fixing - PostgreSQL 9.2

[David G. Johnston]

On Mon, Feb 29, 2016 at 2:56 PM, drum.lu...@gmail.com 
wrote:

> *Question:*
>
> Payments in a Pending state cannot be invoiced and are excluded from the
> Invoice Runs section, but they are showing in the count mechanic.
>
> How can I solve this?
>

​In 9.2 you probably need to convert the count into a conditional sum:

SELECT sum(CASE WHEN  THEN 1 ELSE 0 END) FROM data;

You can probably do the same with count since it excludes nulls.

SELECT count(NULLIF(invoice_status, 'Pending') FROM invoices;

​9.4 introduced a FILTER clause for Aggregate Expressions that can do this
much more cleanly and efficiently.​

​http://www.postgresql.org/docs/9.4/static/sql-expressions.html​

David J.

[GENERAL] Function fixing - PostgreSQL 9.2

[drum.lu...@gmail.com]

There is a number beside each color flag, and a number in the "sub status"
drop down. Instead of "the number of jobs that have billable items that can
be invoiced, it shows the "the number of jobs that have "something"
billable but not yet invoiced."

And since those payments cannot be invoiced alone, they won't show up in
the list.

Let me know if I can provide any further information.

*Question:*

Payments in a Pending state cannot be invoiced and are excluded from the
Invoice Runs section, but they are showing in the count mechanic.

How can I solve this?

Thank you



*Function:*

CREATE OR REPLACE FUNCTION "public"."g_status_types_jobs" ("client_id"
bigint DEFAULT NULL::bigint, "output_order" character varying DEFAULT
'-START_TIME'::character varying, "start_time" timestamp without time
zone DEFAULT NULL::timestamp without time zone, "end_time" timestamp
without time zone DEFAULT NULL::timestamp without time zone,
"statuses" "text" DEFAULT NULL::"text", "status_types" "text" DEFAULT
NULL::"text", "customer_id" bigint DEFAULT NULL::bigint, "user_id"
bigint DEFAULT NULL::bigint, "recurrence_id" bigint DEFAULT
NULL::bigint, "search_str" "text" DEFAULT NULL::"text",
"unscheduled_is_desired" boolean DEFAULT false,
"unassigned_is_desired" boolean DEFAULT false, "templated_status"
boolean DEFAULT false, "by_job_ref" boolean DEFAULT false,
"by_job_description" boolean DEFAULT false, "by_job_address" boolean
DEFAULT false, "by_title" boolean DEFAULT false, "by_status" boolean
DEFAULT false, "by_order_number" boolean DEFAULT false, "by_client"
boolean DEFAULT false, "by_client_notes" boolean DEFAULT false,
"by_billing_client" boolean DEFAULT false, "by_staff" boolean DEFAULT
false, "by_notes_description" boolean DEFAULT false,
"invoiceable_notes_only" boolean DEFAULT false)  RETURNS
TABLE("status_type_id" bigint, "jobs_count" bigint, "job_ids" "text",
"status_type_data" "text")
  STABLEAS $dbvis$SELECT
COALESCE(s.status_type_id, -1) AS status_type_id,
CAST(ROUND(SUM(s.jobs_count)) AS BIGINT) AS jobs_count,
-- we concatenate the lists from all the status labels. some
nullif/substring trickery is required here
CONCAT('{', STRING_AGG(NULLIF(SUBSTRING(s.job_ids FROM 2 FOR
(CHAR_LENGTH(s.job_ids) - 2)), ''), (CASE WHEN (s.job_ids != '{}')
THEN ',' ELSE '' END)), '}') AS job_ids,
(CASE
WHEN (COALESCE(s.status_type_id, -1) != -1) THEN
STRING_AGG(CONCAT(
CAST(s.status_id AS TEXT),
E'\t', REPLACE(REPLACE(s.status_label, E'\t',
''), E'\n', ''),
E'\t', CAST(s.status_is_default AS TEXT),
E'\t', CAST(s.jobs_count AS TEXT),
E'\t', CAST(s.job_ids AS TEXT)
), E'\n')
ELSE
null
END) AS status_type_data
FROM
public.g_statuses_jobs($1, $2, $3, $4, $5, $6, $7, $8, $9,
$10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23,
$24, $25) AS s
GROUP BY
s.status_type_id
;$dbvis$ LANGUAGE sql

Re: [GENERAL] Function in PostgreSQL

[Albe Laurenz]

Sachin Srivastava wrote:
> Do you know if it is possible to create a function in postgres that has 
> errors in the syntax of the
> creation sql? It would help our conversion process if we could get all of our 
> various functions
> present in postgres even if they aren’t in 100% working shape. Is there any 
> way to ignore errors on
> object creation?

I am not 100% sure what you mean, but does

SET check_function_bodies=off;

fix your problem?

Yours,
Laurenz Albe

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

[GENERAL] Function in PostgreSQL

[Sachin Srivastava]

Hi,



Do you know if it is possible to create a function in postgres that has
errors in the syntax of the creation sql? It would help our conversion
process if we could get all of our various functions present in postgres
even if they aren’t in 100% working shape. Is there any way to ignore
errors on object creation?


Please suggest?



Regards,

SS